Creating a SSIS package

HI All,

can any body give steps to import data from one sqldb to another through ssis package, i was comfotable with dts but ssis is a lil bit confusing.....

thnx

regards

Start with the Wizard. Use it to build your first package, then save it and open it up in the designer, and you have your first sample package to learn from.

You actually want the Data Flow task for this, but why not let the Wizard show you for now.

You may find it useful to give these a try to help get you familiar with SSIS

Integration Services Tutorials
(http://msdn2.microsoft.com/en-us/library/0fc6e3a7-1c12-444a-b1ef-ead622f805d2.aspx)

Creating a generic package to import a variable number of columns

Hi,

We are building an application with

a database that contains Jobs. These Jobs have properties like Name, Code etc.

and some custom properties, definable by the application admin. For bulk import

of Jobs, we want to allow the import of an Excel sheet with the columns Name,

Code and a variable amount of columns. If the header names of these columns in

the Excel sheet match the name of a custom property in the system we want to add

the value of that cell into the database as property

value.

In our Data Flow of our Import

Package in SSIS we added an Excel Source that points to a test excel sheet with

the Name and Code columns and – for this example - 3 custom property columns

(Area, Department, Job Family). When we configure the Excel Source in the Excel

Source Editor, we have the option to select the Columns from the Available

External Columns table. But here lays the problem, we do not know at design

time, what custom property columns to expect. We DO expect the Name and Code

columns, but the rest is uncertain at design-time.

That raises the question: Is there

some way to select all of any incoming columns (something like a SELECT * in

T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is

being generated at design-time would need to be updated at run-time to reflect

the variability of the columns that might be encountered while reading the excel

sheet.

Then, we thought, we could add a Script

Component to our data flow that passes some kind of DataSet (or DataReader) in

which we can walk through the columns ourselves? But then still, we miss the

option to include ANY of the columns while reading an Excel sheet (or any other

datasource by the looks of it)

We are aware of the option of

optional columns in combination with the RaggedRight option, but it seems that

we would have to put all of the columns of a row in just one column and then

extract all the columns later with Derived Columns. But then, since the source

import file is being prepared by an application admin, we want don’t want to

burden him with this horrendous task of putting everything in one

column.

We would like to have some way of

iterating through all the columns, either in a Script Component or maybe with a

Pivot/Unpivot mechanism.

Does anyone have any suggestions? Are there other options we should have considered?

The metadata of the pipeline is fixed at design-time. You cannot change the columns at runtime.

-Jamie

|||

Since you're importing from Excel, you may be able to define a dataflow that reads the maximum amount of columns you anticipate ever having in one Excel file. The Excel files with less columns would return empty strings for the non-existent columns.

Haven't tried this, but it might work.

K

Creating a database with an MSDE installation

Hello,
I'm Trying to setup a XP workstation with the msde sql server engine, but I
can't find a way to create a database or import a database with it.
greetings
hi,
rgerla wrote:
> Hello,
> I'm Trying to setup a XP workstation with the msde sql server engine,
> but I can't find a way to create a database or import a database with
> it.
> greetings
MSDE comse with no tools but oSql.exe, a command line tool you can use for
both administrative task and DML operations.. please have a look at
http://support.microsoft.com/default...;EN-US;q325003 for further
info about oSql..
for your convenience, syou can have a look at a prj of mine, at the link
following my sign., which provide a free user interface similar to Microsoft
Enterprise Manager to administer and manage MSDE... further tools, both
commercial and free can be found at
http://www.microsoft.com/sql/msde/partners/default.asp and/or
http://www.aspfaq.com/show.asp?id=2442
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Using DBAMgr To access the MSDE installation I get the follow error while
trying to logon:
-2147203052 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
for user 'sa'. Reason: Not associated with a trusted SQL Server Connection
What am I doing wrong?
"Andrea Montanari" wrote:

> hi,
> rgerla wrote:
> MSDE comse with no tools but oSql.exe, a command line tool you can use for
> both administrative task and DML operations.. please have a look at
> http://support.microsoft.com/default...;EN-US;q325003 for further
> info about oSql..
> for your convenience, syou can have a look at a prj of mine, at the link
> following my sign., which provide a free user interface similar to Microsoft
> Enterprise Manager to administer and manage MSDE... further tools, both
> commercial and free can be found at
> http://www.microsoft.com/sql/msde/partners/default.asp and/or
> http://www.aspfaq.com/show.asp?id=2442
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
rgerla wrote:
> Using DBAMgr To access the MSDE installation I get the follow error
> while trying to logon:
> -2147203052 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login
> failed for user 'sa'. Reason: Not associated with a trusted SQL
> Server Connection
> What am I doing wrong?
MSDE installs by default enabling only WinNT (trusted) connections and
disabling standard SQL Server connections (userid=xxx; password=xxx)
you can modyfy this behaviour both at install time, specifying the
SECURITYMODE=SQL parameter to the setup.exe MSDE boostrap installer, or
later, at run time, modifying a Windows registry key,
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
LoginMode=2
for a default instance,
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
LoginMode=2
for a named instance...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply