Import of SPSS-files

Hello

i'm trying to find out if SSIS is a right toolbox for importing of many SPSS-files into the large market research data warehouse.

A simplified version of SPSS-datasheet looks like this:

- many columns (up to 3000). some of them are interview attributes (InterviewID, Date, etc.) and variables, representing survey questions

- relative few rows with "cases" or "interviews"

- in cells there are answers to the questions (in columns) by respondent (in rows)

As first step i would like to unpivot the dataset in the following way:

- Pass-Through: all interview attributes, that shouldn't be unpivoted

- Input Columns: rest 2990 columns

- Destination Column: Question

- Pivot Key Value Column Name: Answer

It works allright, except that i has to manually define destination column for all 2990 input columns, which takes a lot of time (multiplied by the number of SPSS-files i want to import). Is there a way to automate this (default value for destination column and/or scripting?)

Many thanks for your help!

If you hook your data flow up to an OLE DB destination, there is an option to create the table to match the incoming meta data. This works well and I use it all of the time. Just click the "New..." button next to the table name drop down box.|||

Thanks for your tip. But I think i cannot upload the data without unpivoting it first. I run into 1024 columns pro table restriction of SQL Server.

Again, is there a way to set up "destination column" for all input columns of unpivot transformation automatically?

Many thanks.

|||

Denis Zorenko wrote:

Thanks for your tip. But I think i cannot upload the data without unpivoting it first. I run into 1024 columns pro table restriction of SQL Server.

Again, is there a way to set up "destination column" for all input columns of unpivot transformation automatically?

Many thanks.

Oh, sorry, I misunderstood. No, there is not. Perhaps you could write your own application to build an SSIS package programmatically, but using the out-of-the-box functionality will not allow you to do what you wish.|||

Many thanks Phil!

Does that mean that the script component won't do the job?

|||Sure. The script component could do it, but it'll be a long, drawn out challenge, I'd presume. Well, the problem is that the script component would need to know its output columns upfront, so you'd still have work ahead of you.|||

Well, the output columns are known. They are InterviewID, Question, Answer.

"InterviewID" is a column of the datasheet (pass-through). Its name could be passed as variable.

"Question" contains all other column names (pivot key value column name)

"Answer" contains the values of the datasheet cells (destination column)

What would be the best way to start out?

Many thanks

|||

Check this post and see if it works for you:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

Seems like it would be a good fit for your scenario.

|||Thanks. That will help me to start out.

import large field from SSIS

Hi,

I am making a SSIS package that imports data from a application using a custom ODBC driver. The field in the application is set to be a "longvarchar" type field and can be from 2 characters to 2MB of data.

I've created a ODBC data connection in the SSIS package and use a "DataReader Source" to read the data I need. The sql statement is very simple

Select log from tablename

When I try to run the SSIS package with that statement it just goes to yellow on the DataReader Source and stops. It stays like that until I stop it. If I select other fields except for that field it works fine. Also I've been able to get it to succeed getting the log field if I select a log record that's not too big. The largest one I've been able to get is 800 characters, but I got one with 2500 characters that just stops on yellow.

In the Progress log the last line says:

[DTS.Pipeline] Information: Execute phase is beginning.

Does anyone have any ideas on how to resolve this?
Have you check the ValidateMetadeta properties of data source Is there any warning message appear in you data reader ?|||I've tried both with having the ValidateMetaData option to false and true but it doesn't make any difference. There is no error/warning messages in the progress log and I don't know of any other places to look for error messages.

This is really starting to annoy me, but this is the only way we can get the data out of that system so I need this to work...
|||

Hi,

DataReaderSrc is not particularly efficient about dealing with BLOB data, such as DT_NTEXT or DT_IMAGE columns. It may be that it is just being slow...

Unfortunately, DataReaderSrc does not utilize the perf counters for BLOB bytes read -- this is a known issue that is planned to be fixed in a future release. If you look at perfmon while the package is running, what's happening with the CPU and memory usage?

mark

|||Thanks for your answer. When looking at the perfmon and the task manager while running the package I was a bit surprised.
First of in the perfmon the Memory object is steady on 0, the Physical Disk object is going up and down from 0 to 20 and then there is the occasional spike up to 100.
Then there is the processor object which stays at around 50 constantly. Looking at the task manager the process: "DtsDebugHost.exe" is staying at 50% CPU and using 30.976 K memory. Is this normal when executing a package?

I was thinking too that it might just be slow. But I changed my query to only select 1 record based on the id of the record and it still stays on for 20 min+ (I stopped it after that). If I select a record that got less data in the BLOB field then it completes within 10 seconds.

My development server is a Intel Xeon dual 3.6 GHz with 3 GB memory so I don't think our server is good enough

If the future fix will fix this issue that will be good enough, because I sort of told people that we have to find a different way to create the reports.
|||

The "future fix" I spoke of was just to add the performance counters to DataReaderSrc, which would help diagnose issues like this one.

I will try to set up a similar scenario here to see if I can reproduce the behaviour you are seeing.

thanks

Mark

|||

Hi Josh,

I created a package with a datareader source, and used connections of the type:

.NET Providers\SqlClient Data Provider

.NET Providers\Odbc Data Provider

In both cases, i was able to read 20 Mb of TEXT data in a second or two.

Is it possible for you to try using a different driver/provider?

Can you try using some other application with your driver/provider to see if you can read the data or if you have the same problem?

thanks

Mark

import large field from SSIS

Hi,

I am making a SSIS package that imports data from a application using a custom ODBC driver. The field in the application is set to be a "longvarchar" type field and can be from 2 characters to 2MB of data.

I've created a ODBC data connection in the SSIS package and use a "DataReader Source" to read the data I need. The sql statement is very simple

Select log from tablename

When I try to run the SSIS package with that statement it just goes to yellow on the DataReader Source and stops. It stays like that until I stop it. If I select other fields except for that field it works fine. Also I've been able to get it to succeed getting the log field if I select a log record that's not too big. The largest one I've been able to get is 800 characters, but I got one with 2500 characters that just stops on yellow.

In the Progress log the last line says:

[DTS.Pipeline] Information: Execute phase is beginning.

Does anyone have any ideas on how to resolve this?
Have you check the ValidateMetadeta properties of data source Is there any warning message appear in you data reader ?|||I've tried both with having the ValidateMetaData option to false and true but it doesn't make any difference. There is no error/warning messages in the progress log and I don't know of any other places to look for error messages.

This is really starting to annoy me, but this is the only way we can get the data out of that system so I need this to work...
|||

Hi,

DataReaderSrc is not particularly efficient about dealing with BLOB data, such as DT_NTEXT or DT_IMAGE columns. It may be that it is just being slow...

Unfortunately, DataReaderSrc does not utilize the perf counters for BLOB bytes read -- this is a known issue that is planned to be fixed in a future release. If you look at perfmon while the package is running, what's happening with the CPU and memory usage?

mark

|||Thanks for your answer. When looking at the perfmon and the task manager while running the package I was a bit surprised.
First of in the perfmon the Memory object is steady on 0, the Physical Disk object is going up and down from 0 to 20 and then there is the occasional spike up to 100.
Then there is the processor object which stays at around 50 constantly. Looking at the task manager the process: "DtsDebugHost.exe" is staying at 50% CPU and using 30.976 K memory. Is this normal when executing a package?

I was thinking too that it might just be slow. But I changed my query to only select 1 record based on the id of the record and it still stays on for 20 min+ (I stopped it after that). If I select a record that got less data in the BLOB field then it completes within 10 seconds.

My development server is a Intel Xeon dual 3.6 GHz with 3 GB memory so I don't think our server is good enough

If the future fix will fix this issue that will be good enough, because I sort of told people that we have to find a different way to create the reports.
|||

The "future fix" I spoke of was just to add the performance counters to DataReaderSrc, which would help diagnose issues like this one.

I will try to set up a similar scenario here to see if I can reproduce the behaviour you are seeing.

thanks

Mark

|||

Hi Josh,

I created a package with a datareader source, and used connections of the type:

.NET Providers\SqlClient Data Provider

.NET Providers\Odbc Data Provider

In both cases, i was able to read 20 Mb of TEXT data in a second or two.

Is it possible for you to try using a different driver/provider?

Can you try using some other application with your driver/provider to see if you can read the data or if you have the same problem?

thanks

Mark

Import from Excel with IMEX=1 still gives probs

Hi:

Am trying to import XLS data into SQL 2005 SP2 thro a SSIS Data Flow task. My Excel Connection string has IMEX=1, ImportMixedTypes is set to Text and the typeguessrows is set to 0.

Import works fine for cells of Format Text, but when I have a large number (in a general Format cell) it gets converted into scientific notation(e.g. 3.234175e+7) in the table.

What am I doing wrong?

TIA

Kar

What's the type of the column in the data flow?|||

The column is of type DT_WSTR(255) in the data flow. There's no prob with cells that are textual, or are numeric with Text Format. Am only having a prob with cells with General Format I think. These cells look normal in excel, but when I import to Ole DB, I get Scientific Notation.

I have tried to save Excel as Text, and that works, but that will introduce unnecessary additional layers to build, test and maintain. Hope to find a solution within the box.

TIA

Kar

Import Flat Files

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

Hosam Abd EL-Wahab wrote:

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

You can run a foreach loop in your control flow to loop over every file...

No, you can't do a select * from flatfile where.... However, you can chose which columns to import and then you can use conditional splits to enforce your logic.|||

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

|||

Hosam Abd EL-Wahab wrote:

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

You can't. Not with a flat file source. Sorry.|||

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

|||

Hosam Abd EL-Wahab wrote:

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

Right, if you want to code your own script. And FYI, that link is for DTS, not SSIS.

I would argue that simply loading a flat file and using a conditional split in the data flow will be extremely fast, almost such that trying to code something to exclude records up front would not be warranted.sql

Import Flat Files

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

Hosam Abd EL-Wahab wrote:

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

You can run a foreach loop in your control flow to loop over every file...

No, you can't do a select * from flatfile where.... However, you can chose which columns to import and then you can use conditional splits to enforce your logic.|||

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

|||

Hosam Abd EL-Wahab wrote:

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

You can't. Not with a flat file source. Sorry.|||

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

|||

Hosam Abd EL-Wahab wrote:

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

Right, if you want to code your own script. And FYI, that link is for DTS, not SSIS.

I would argue that simply loading a flat file and using a conditional split in the data flow will be extremely fast, almost such that trying to code something to exclude records up front would not be warranted.

Import Export Wizard and Unicode columns

Hello Folks,

This is my first real exposure to using SSIS's Import Export Tool.

I am trying to create a package to move the data from a number of tables in SQL Server into a duplicate database in Oracle. I am invoking the Import/Export WIzard from within the Management Studio. I am using the SQL Server native client on the SQL Server side and the Microsoft OLE DB driver for Oracle for the Oracle database. Both of my tables have unicode data types. On the SQL Server side I have columns defined as NVARCHAR and on Oracle the same column is defined as NVARCHAR2.

When I initially select the table for export the wizard assumes that I want to create a new table. The new table has the correct column name, column order and data types (NVARCHAR2). I cannot tell it to append the data. If I add the table owner to the detination table name or use the GUI to pick it with the table owner...the data types for the NVARCHAR2 columns go away. I cannot edit the data types at this point and if I continue, SSIS barks at me that it doesn't know the data types of those columns. The problem also occurs if you use the wizard from inside of the BIDS. It seems to be OK with the data type unless the table pre-exists. This seems un-useful to me.

Is there a way to avoid this in the Import/Export Wizard? Am I doing something wrong?

Any help would be appreciated.

Thanks, Mark

Hi Mark,

could you double check the metadata of your preexisting destination table matches the incoming metadata? Also, could you post the error the wizard reports?

Thanks,

Bob

|||Hi Bob,

Well, as I mentioned before if I try to continue SSIS barks and says:

TITLE: SQL Server Import and Export Wizard


Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


[sql_dev_slove2].[dbo].[ACCESSPROFILE] -> "MARKT"."ACCESSPROFILE":

- The data type could not be assigned to the column "PROFILENM" in "Microsoft OLE DB Provider for Oracle".
- The data type could not be assigned to the column "PROFILEDSC" in "Microsoft OLE DB Provider for Oracle".

After this point, I can go no farther so (I think) I cannot see the actual meta data. If I do not change the owner (let SSIS think it needs to create the table) the input data type is DT_WSTR. The tables are defined as follows with the PROFILENM and PROFILEDSC columns being the troublesome ones:

SQL Server:
===========

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID INTEGER NOT NULL ,
PROFILENM NVARCHAR(50) NOT NULL ,
PROFILEDSC NVARCHAR(250) NULL ,
DEFAULTSW INTEGER DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID INTEGER NOT NULL ,
UPDATEDTM DATETIME DEFAULT GETDATE() NOT NULL ,
VERSIONCNT INTEGER DEFAULT 1 NOT NULL ,
ALLOWALLSW INTEGER DEFAULT 0 NOT NULL
)
;

Oracle:
=======

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID NUMBER(10) NOT NULL ,
PROFILENM NVARCHAR2(50) NOT NULL ,
PROFILEDSC NVARCHAR2(250) NULL ,
DEFAULTSW NUMBER(10) DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID NUMBER(10) NOT NULL ,
UPDATEDTM DATE DEFAULT SYSDATE NOT NULL ,
VERSIONCNT NUMBER(10) DEFAULT 1 NOT NULL ,
ALLOWALLSW NUMBER(10) DEFAULT 0 NOT NULL
)
/

Does this help?|||

Mark,

could you try the same thing using the Oracle's own OLE DB provider? Microsoft OLE DB provider for Oracle is pretty old one and I suspect it does not even know about nvarchar2 data type (I am not able to check this at the moment though).

There is a fundamental difference between transfering data into an existing or a new table. The existing table has the metadata already defined while the wizard generates new tables.

HTH,

Bob

Import Export Wizard and Unicode columns

Hello Folks,

This is my first real exposure to using SSIS's Import Export Tool.

I am trying to create a package to move the data from a number of tables in SQL Server into a duplicate database in Oracle. I am invoking the Import/Export WIzard from within the Management Studio. I am using the SQL Server native client on the SQL Server side and the Microsoft OLE DB driver for Oracle for the Oracle database. Both of my tables have unicode data types. On the SQL Server side I have columns defined as NVARCHAR and on Oracle the same column is defined as NVARCHAR2.

When I initially select the table for export the wizard assumes that I want to create a new table. The new table has the correct column name, column order and data types (NVARCHAR2). I cannot tell it to append the data. If I add the table owner to the detination table name or use the GUI to pick it with the table owner...the data types for the NVARCHAR2 columns go away. I cannot edit the data types at this point and if I continue, SSIS barks at me that it doesn't know the data types of those columns. The problem also occurs if you use the wizard from inside of the BIDS. It seems to be OK with the data type unless the table pre-exists. This seems un-useful to me.

Is there a way to avoid this in the Import/Export Wizard? Am I doing something wrong?

Any help would be appreciated.

Thanks, Mark

Hi Mark,

could you double check the metadata of your preexisting destination table matches the incoming metadata? Also, could you post the error the wizard reports?

Thanks,

Bob

|||Hi Bob,

Well, as I mentioned before if I try to continue SSIS barks and says:

TITLE: SQL Server Import and Export Wizard


Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


[sql_dev_slove2].[dbo].[ACCESSPROFILE] -> "MARKT"."ACCESSPROFILE":

- The data type could not be assigned to the column "PROFILENM" in "Microsoft OLE DB Provider for Oracle".
- The data type could not be assigned to the column "PROFILEDSC" in "Microsoft OLE DB Provider for Oracle".

After this point, I can go no farther so (I think) I cannot see the actual meta data. If I do not change the owner (let SSIS think it needs to create the table) the input data type is DT_WSTR. The tables are defined as follows with the PROFILENM and PROFILEDSC columns being the troublesome ones:

SQL Server:
===========

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID INTEGER NOT NULL ,
PROFILENM NVARCHAR(50) NOT NULL ,
PROFILEDSC NVARCHAR(250) NULL ,
DEFAULTSW INTEGER DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID INTEGER NOT NULL ,
UPDATEDTM DATETIME DEFAULT GETDATE() NOT NULL ,
VERSIONCNT INTEGER DEFAULT 1 NOT NULL ,
ALLOWALLSW INTEGER DEFAULT 0 NOT NULL
)
;

Oracle:
=======

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID NUMBER(10) NOT NULL ,
PROFILENM NVARCHAR2(50) NOT NULL ,
PROFILEDSC NVARCHAR2(250) NULL ,
DEFAULTSW NUMBER(10) DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID NUMBER(10) NOT NULL ,
UPDATEDTM DATE DEFAULT SYSDATE NOT NULL ,
VERSIONCNT NUMBER(10) DEFAULT 1 NOT NULL ,
ALLOWALLSW NUMBER(10) DEFAULT 0 NOT NULL
)
/

Does this help?|||

Mark,

could you try the same thing using the Oracle's own OLE DB provider? Microsoft OLE DB provider for Oracle is pretty old one and I suspect it does not even know about nvarchar2 data type (I am not able to check this at the moment though).

There is a fundamental difference between transfering data into an existing or a new table. The existing table has the metadata already defined while the wizard generates new tables.

HTH,

Bob

sql

Import excel file to database

Hai,

I am new to SSIS 2005.

Now i would like to use the foreach loop structure in an SSIS package to
loop through however many Excel files are placed in a directory and
then perform an import operation into a SQL table on each of these
files sequentially.

But i dont know how to get start?

Can anyone guide me on this task?

Thanks.

Step (1) and (2) used from http://rafael-salas.blogspot.com/

If Each Excel File has a Single Work Sheet, The following will be the method:

1) In the foreach loop properties use the following settings

In Collections tab,

Set Enumerator to "Foreach file Enumerator",

Under enumerator Configurations, Specify the Folder and Filter files with *.xls

In Variables Mapping tab,

Under Variable Column Select A Variable Name - User::ExcelFilePath(of String type, ForEachloop Scope) and Index Value 0.

2) Under the Dataflow, Under Source OLEDB Connection manager properties, use the expression builder to assign the Connection String Value to @.[User::ExcelfilePath].

Connect to source to your Destination SQL Server

For the rest of the steps, we pursue from step 11 given by DouglasL'S Answer

Import DT_DBDATE into a SQL TAble with datatype of datetime but without the timestamp

I created a SSIS package and creating a derived column named: Date...set datatype as DT_DBDATE....I do not want the timestamp on date...then I want to load this Date into a SQL server database table, with datatype of datetime, but it will load here with the timestamp which I do not want. Any ideas? I did change datatype of the SQL Server Destination datatype to DT_DBDATE but it will change it back to DT_DBTimestamp. thxSQL Server has no data type that contains just a date. If you insert just a date (insert into table (your_datefield) values ('07/16/2007')) you'll get a result stored in the database as '07/16/2007 00:00:00' or something like that. No way around that at the moment. Keep your eyes on SQL Server 2008 though.

Import data from SQL Server 2000 incredibly slow

I have a problem with bad perfomance with my import of data from a SQL Server 2000 database. I use an OLEDB datasource in my SSIS package to connect to the sql server 2000 database. My 2005 server runs 64bits but i dont think this is an issue. With this configuration the import is VERY slow, we are talking about 40+ minutes to get 3.5 million rows with about 20 columns. When i create a test DTS package on the SQL Server 2000 server itself and run it, its blazingly fast. Has anyone run into something similar?

Have you done any debuggin of where the bottleneck occurs? Donald Farmer explains some useful ways to do this, see here:

SSIS: Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

-Jamie

|||I had a look at the webcast. While it contains a lot of good stuff on optimization it doesnt really help me in this particular case. I tested the SSIS package against a mirror of the production server on another box, and it ran about 10X faster. So there has to be somenthing with the way the server is set up.

Import data from Excel?

Hello,

I have an Excel spreadsheet that I am loading data from and I want to prevent SSIS from making assumptions about the data contained within the spreadsheed and to just treat every column as a string (i.e. Unicode string [DT_WSTR]). How is this done?

I know I could do a conversion once the data is loaded, but I am wondering if there is a way to specify this in the Excel Source settings without having to add a Data Conversion task to the Data Flow.

TIA...

As far as I know, this cannot be done. You are allowed to set data types in Flat File Sources and delimit them by a comma, though.

Maybe you could export the excel file to comma delimited and then import as a Flat File.

Just a suggestion,

Mark

https://spaces.msn.com/mgarnerbi

|||

You could experiment with the following settings:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

import ascii file with ssis and script

Hi, i've question about how to import an ascii-file in a sql 2005 table.
I want to import this file also with an unique key. There i first have to get the last key form the table and then raise this key. Next step is to use this key during the import.

How do i have to do this in ssis?
Thanks in advance

OlafNo problem.

While in your control flow, add a variable named MaxKey of integer type. Then in your control flow, right before the data flow task, add an Execute SQL task. Set its ResultSet to Single row. Setup the connection in it to point to your database and the for the SQLStatement, use: "select max(keyfield) from your_table". Click on the Result Set option on the left-hand side of the editor. Set the result name to 0 and chose User::MaxKey as the Variable Name. Click OK.

In your data flow right before the OLE destination, add a Script Component. Chose to use it as a transformation. Edit it. On the left, select "Inputs and Outputs". Expand Output 0 and then "Add Column" to the Output Columns folder and call it "NewKey. Make sure it is an integer data type big enough to hold a key big enough for your table. Then click on "Script" on the left to bring up the script parameters. Add "MaxKey" to the ReadOnlyVariables box. Then, click on the Design Script... button. Here's your script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in

' NextKey will always be zero when we start the package.
' This will set up the counter accordingly
If (NextKey = 0) Then
' Use MaximumKey +1 here because we already have data in the table, or we'll start with 0+1=1 if we don't
' and we need to start with the next available key
NextKey = MaximumKey + 1
Else
' Use NextKey +1 here because we are now relying on
' our counter within this script task.
NextKey = NextKey + 1
End If

Row.NewKey = NextKey ' Assign NextKey to our AdFormKey field on our data row
'
End Sub

End Class|||Phil,
Thansks for your answer, and i'm doing wel till step 'Script Component'. After i've clicked on 'script' and add 'MaxKey', i get an error message after i've clicked on button 'Design Script'. The message is: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container...' What do i wrong?
Thanks.

Olaf|||See the following post which explains the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1

In short, save a copy of the SSIS variable (e.g. Me.Variables.Whatever ) in the OnPreExecute subroutine. Use the copy in lieu of Me.Variables.Whatever in you ProcessInputRow() subroutine.|||thanks jaegd, problem is solved. My i ask you an other question?
I get an error message 'Cannot create connector. the destination component does not hav any available inputs for use in creating a path' after i've connect the script component at the destination ole. What goes wrong? When i look to the properties of the OLE, i see some unuased input colummns.
Thanks in advance|||

jaegd wrote:

See the following post which explains the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1

In short, save a copy of the SSIS variable (e.g. Me.Variables.Whatever ) in the OnPreExecute subroutine. Use the copy in lieu of Me.Variables.Whatever in you ProcessInputRow() subroutine.

I don't think this is the problem. I detailed the steps exactly as I use it. Either the OP has a typo, or he didn't define the variable in the right scope. The variable must be scoped to the package. The solution above is too much work.|||

Olaf vd Sanden wrote:

Phil,
Thansks for your answer, and i'm doing wel till step 'Script Component'. After i've clicked on 'script' and add 'MaxKey', i get an error message after i've clicked on button 'Design Script'. The message is: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container...' What do i wrong?
Thanks.

Olaf

You must define the variable when in the control flow. Make sure that the variable's scope is set to the package.

Then make sure you type the variable name in the ReadOnlyVariables section on the script parameters page, as I have indicated in my instructions. Do not include any extra spaces when typing this variable name in the ReadOnlyVariables box.|||

Olaf vd Sanden wrote:

thanks jaegd, problem is solved. My i ask you an other question?
I get an error message 'Cannot create connector. the destination component does not hav any available inputs for use in creating a path' after i've connect the script component at the destination ole. What goes wrong? When i look to the properties of the OLE, i see some unuased input colummns.
Thanks in advance

Please make sure you are using a destination OLE connector and not a source connector.|||Phil, First i had a data conversion before my destionation ole. After i've removed this is was possible to connect the script component. Sorry for my questions, it's new for me. But, isn't possible to use a data conversion and a script component?
Thanks, Olaf|||

Olaf vd Sanden wrote:

Phil, First i had a data conversion before my destionation ole. After i've removed this is was possible to connect the script component. Sorry for my questions, it's new for me. But, isn't possible to use a data conversion and a script component?
Thanks, Olaf

Sure it's possible. You must be sure that when you dropped in the script component that you selected "Transformation," not "Source" or "Destination."|||Phil, the script component propertie is 'Transformation', but still i can't connect a data covnversion and a script component add an Ole DB Destination
The situation that i want in the data flow is:
a 'Flate File source editor' > 'Data Conversion Tranformation editor' > 'OLE DB destination Editor' and also the 'Script Component' > 'OLE DB destination Editor'

Sorry again, but i'm new with this.
Thanks in advance.

Olaf|||

Olaf vd Sanden wrote:

Phil, the script component propertie is 'Transformation', but still i can't connect a data covnversion and a script component add an Ole DB Destination
The situation that i want in the data flow is:
a 'Flate File source editor' > 'Data Conversion Tranformation editor' > 'OLE DB destination Editor' and also the 'Script Component' > 'OLE DB destination Editor'

Sorry again, but i'm new with this.
Thanks in advance.

Olaf

Your OLE DB destination has to be at the END of the dataflow. You cannot have two in the same dataflow, unless you've split the dataflow with a multicast, conditional statement, etc...

Why do you have the OLE DB destination between the Data Conversion transformation and the Script component?|||Phil, Thanks for your reply. I think i've solved the problem. I've used an 'Union All' where i connected the 'Flate File source editor' and 'Script Component'. After the 'Union' comes a 'Data conversion' and then the 'OLE DB Destination'. I like to hear from you if this the right way.

Now i get an error message in the 'Execution Results': [DTS.Pipeline] Error: component "Union All" (14793) failed the pre-execute phase and returned error code 0x80070057.

What do I do wrong now?
Thanks.

Olaf|||Your flat file should hook into the data conversion transformation and then to the script component and then to the OLE DB destination.

FF -> Data Conversion -> Script Component -> OLE DB Destination.

No union is necessary.|||Phil, Thanks again for your fast reply. And it works.
Again thanks.
Olaf

Import and Export Wizard: transferring multiple tables from SQL Server 2005 to SQL Server 2000

Hi!

I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.

I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)

Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.

Basically the wizard creates the following Control Flow tab entries (in processing sequence order):

an Execute SQL Task: NonTransactableSql an Execute SQL Task: START TRANSACTION a Sequence Container: Transaction Scoping Sequence, which contains an Execute SQL Task: AllowedToFailPrologueSql an Execute SQL Task: PrologueSql a Foreach Loop Container, which contains a Transfer Task with an icon I did not notice in the Toolbox an Execute Package Task: Execute Inner Package an Execute SQL Task: EpilogueSql an "on success" arrow to an Execute SQL Task: COMMIT TRANSACTION an Execute SQL Task: PostTransaction Sql an "on failure" arrow to an Execute SQL Task: ROLLBACK TRANSACTION an Execute SQL Task: CompensatingSql

Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?

This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.

So now I'm trying to understand why the Wizard created this more-complex package.

Any help will be appreciated, including references to non-Microsoft books/websites/etc.

Thanks in advance.

Dan

Hi Dan,

you can also have a package with 50 parallel data flows built if you uncheck the "Optimize for Many Tables" checkbox. That might be easier for you to edit. This solution does not scale too well with "really" too many tables so we had to emloy the complex package you are referring to. The metadata is stored in the XML file you mentioned and the transfer task goes through that file and generates simple data flows on the fly and executes them one by one.

HTH.

|||

Bob,

Thanks.

I also now know how to get the "50 parallel data flows" that is easier to edit.

I don't understand, though, how the metadata in the XML would "tag along" if I were to save the SSIS package to the File System and copy it to a network drive, where others could use it.

Dan

|||

You would need to copy all the associated files (like the XML with metadata) and tweak the connections to point to the new location.

Thanks.

|||

Bob,

Thanks again.

So it seems the Wizard creates an SSIS package, and also whatever files are needed to support that package, e.g., the XML file with the table names and structures (if a CREATE is necessary). The user of the Wizard must be smart enough to realize that anything in the Connection Manager must accompany the SSIS package -- and that such "temporary folder" files are essential to the task (so they should not be deleted by any "housekeeping" effort on the PC). That's good to know.

I would have expected such files to end up in someplace like the BIN folder found in the same folder where the SSIS package is created. If it isn't a long explanation, maybe you might share why the development team did not use the BIN folder for such files. (I am not wanting to be "nasty" -- just curious about how such decisions are made by the development team.)

Dan

|||

Hi Dan,

the transfer tables task was not initially designed to be used by the wizard. It was built by SMO team to allow copying tables using their APIs. When used that way the internal package is invisible and it makes sense to put the additional files to the temp folders. Later, we realized it might be suitable for our purpose. We weren't sure how often our users would actually want to preserve this package.

We realized it is a problem now and found some additional issues with the table transfer provider task, so we are looking into simplifying the wizard generated packages for Katmai.

Thanks,

-Bob

|||

Bob,

Thanks for the response. I truly appreciate the help the Wizard has given me on many occasions.

I have learned a lot from the Wizard, using it to convert my SQL Server 2000 DTS Packages to SSIS, and learning from the packages it created.

Since you mentioned some new features, is there any current plan to enhance the Execute Package Utility so that it can accommodate the movement of data from MS Access 2003 SP2 to SQL Server 2005? I can do that when I use Visual Studio 2005, but when I try to execute the package with the Execute Package Utility I get many errors of the form

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion 1" (49).

The only data conversion I perform is double-byte characters to single-byte characters.

(Maybe I should ask this in a separate thread?)

Dan

|||

Are trying to run the package on the same machine it worked from the designer? What edition of the product you have installed?

It does not seem like new features are needed for this. You just need a proper edition of the product on the machine where you run the package.

Thanks.

|||

Hi Bob,

I'm having a similar issue as Dan, with two notable differences. First, I have over 300 tables to transfer and Second, most of the tables have identity columns.

When I went through the wizard, with "Optimize for Many Tables" checked, it ignored the adjustments to accept the identity values and created new values. Is there a way to adjust this script to accept the Identity values without setting up 300+ parallel data flows? The components are identical to what Dan described in the first posting.

If this can't be done, other suggestions are welcome.

Thanks - Gary

|||

Hi Gary,

unfortunately you have hit another issue with the transfer tables task I was referring to in the previous post. Currently, it is not possible to pass the identitty column settings when the "Optimize for many tables" option is selected.

The best workaround I can offer is to copy your tables in multiple batches (50 tables each should work, it might take up to 100 depending on your hardwear but you would need to test it) with unchecked "Optimize for many tables" option.

Thanks.

|||

Bob,

Thanks for your reply.

Yes, I am trying to run the package from the same machine it worked from the designer. But I am trying to run a "file system" copy of the package that I placed on the network drive. I am out of the office at the moment, so I cannot try running the exact copy on my PC hard drive -- but I will be back in the office in a few days, and can try doing so at that time.

We upgraded to SP2 a month or two ago, for SQL Server 2005. Did you need more "edition" information? If so, I will provide it on Friday, or so.

I just figured it (transfer from Access, and convert Access tables with single-byte characters to double-byte characters, as seem to be usual for SSIS input, then back to single-byte characters for placement in the SQL Server 2005 tables) was a capability that went beyond the intent of the "standalone" package runner (outside Visual Studio), Execute Package Utility.

I am pleased to learn that I may not need to use Visual Studio 2005 to perform this movement of data from Access to SQL Server 2005.

Dan

|||

Bob,

I am back at my desk, where I tried to run the SSIS package that moves approx. 50 tables from MS Access to SQL Server 2005.

The Access version is 2003 (11.6566.8132) SP2.

The SQL Server version is 9.0.3042

The only version information I see in the About box for "About DTExecUI" is "Version: 1.0". Is there some other place I should be seeking version information for this product?

Dan

|||

Dan,

I was asking about the edition of your SQL server instalation; is it Developer, Standard or Enterprise edition?

Thanks.

|||

Bob,

We have the Enterprise edition of SQL Server 2005 in the environment where I am trying to perform the task.

Dan

|||

Have you installed the entire SSIS module on all of those machines as well?

Thanks,

Bob

import an excel file

I tried to import an excel file using import/export wizard. When I launch
it, it gives me an error:
The SSIS Runtime object could not be created. Verify that DTS.dll is avaible
and registered. The wizard cannot continue and will terminate.
Additional Information:
Unable to cast COM object of type ...
Can anyone please tell me how to fix this? Thanks.
Hi
You should find dts.dll in C:\Program Files\Microsoft SQL
Server\90\DTS\Binn\DTS.dll. You could try unregistering in re-registering
this using regsvr32 e.g. regsvr32 C:\Program Files\Microsoft SQL
Server\90\DTS\Binn\DTS.dll
Run this with the /u flag will unload the dll if necessary.
If you are not using SP1 you may want to load this to see if the problem is
resolved. If this does not cure the issue please post error numbers and the
full error message and any information from the event log as well.
HTH
John
"00KobeBrian" wrote:

> I tried to import an excel file using import/export wizard. When I launch
> it, it gives me an error:
> The SSIS Runtime object could not be created. Verify that DTS.dll is avaible
> and registered. The wizard cannot continue and will terminate.
> Additional Information:
> Unable to cast COM object of type ...
>
> Can anyone please tell me how to fix this? Thanks.
>
>
|||Just want to clarify.
So do you want me to run regsvr32 C:\Program Files\Microsoft SQL
Server\90\DTS\Binn\DTS.dll /u
Then run regsvr32 C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll
and see if it works?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D0926A82-FDA8-467A-A56A-6DC1FBE3CE21@.microsoft.com...[vbcol=seagreen]
> Hi
> You should find dts.dll in C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll. You could try unregistering in re-registering
> this using regsvr32 e.g. regsvr32 C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll
> Run this with the /u flag will unload the dll if necessary.
> If you are not using SP1 you may want to load this to see if the problem
> is
> resolved. If this does not cure the issue please post error numbers and
> the
> full error message and any information from the event log as well.
> HTH
> John
> "00KobeBrian" wrote:
|||I got it now. Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D0926A82-FDA8-467A-A56A-6DC1FBE3CE21@.microsoft.com...[vbcol=seagreen]
> Hi
> You should find dts.dll in C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll. You could try unregistering in re-registering
> this using regsvr32 e.g. regsvr32 C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll
> Run this with the /u flag will unload the dll if necessary.
> If you are not using SP1 you may want to load this to see if the problem
> is
> resolved. If this does not cure the issue please post error numbers and
> the
> full error message and any information from the event log as well.
> HTH
> John
> "00KobeBrian" wrote:
|||Hi
Does this mean it is working?
Running regsvr32 C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll
whilst it is registered would not cause any problems, but it is a good idea
to try and unregister it first.
John
"00KobeBrian" wrote:

> I got it now. Thanks.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D0926A82-FDA8-467A-A56A-6DC1FBE3CE21@.microsoft.com...
>
>

import an excel file

I tried to import an excel file using import/export wizard. When I launch
it, it gives me an error:
The SSIS Runtime object could not be created. Verify that DTS.dll is avaible
and registered. The wizard cannot continue and will terminate.
Additional Information:
Unable to cast COM object of type ...
Can anyone please tell me how to fix this? Thanks.Hi
You should find dts.dll in C:\Program Files\Microsoft SQL
Server\90\DTS\Binn\DTS.dll. You could try unregistering in re-registering
this using regsvr32 e.g. regsvr32 C:\Program Files\Microsoft SQL
Server\90\DTS\Binn\DTS.dll
Run this with the /u flag will unload the dll if necessary.
If you are not using SP1 you may want to load this to see if the problem is
resolved. If this does not cure the issue please post error numbers and the
full error message and any information from the event log as well.
HTH
John
"00KobeBrian" wrote:

> I tried to import an excel file using import/export wizard. When I launch
> it, it gives me an error:
> The SSIS Runtime object could not be created. Verify that DTS.dll is avaib
le
> and registered. The wizard cannot continue and will terminate.
> Additional Information:
> Unable to cast COM object of type ...
>
> Can anyone please tell me how to fix this? Thanks.
>
>|||Just want to clarify.
So do you want me to run regsvr32 C:\Program Files\Microsoft SQL
Server\90\DTS\Binn\DTS.dll /u
Then run regsvr32 C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll
and see if it works?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D0926A82-FDA8-467A-A56A-6DC1FBE3CE21@.microsoft.com...[vbcol=seagreen]
> Hi
> You should find dts.dll in C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll. You could try unregistering in re-registering
> this using regsvr32 e.g. regsvr32 C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll
> Run this with the /u flag will unload the dll if necessary.
> If you are not using SP1 you may want to load this to see if the problem
> is
> resolved. If this does not cure the issue please post error numbers and
> the
> full error message and any information from the event log as well.
> HTH
> John
> "00KobeBrian" wrote:
>|||I got it now. Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D0926A82-FDA8-467A-A56A-6DC1FBE3CE21@.microsoft.com...[vbcol=seagreen]
> Hi
> You should find dts.dll in C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll. You could try unregistering in re-registering
> this using regsvr32 e.g. regsvr32 C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll
> Run this with the /u flag will unload the dll if necessary.
> If you are not using SP1 you may want to load this to see if the problem
> is
> resolved. If this does not cure the issue please post error numbers and
> the
> full error message and any information from the event log as well.
> HTH
> John
> "00KobeBrian" wrote:
>|||Hi
Does this mean it is working?
Running regsvr32 C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll
whilst it is registered would not cause any problems, but it is a good idea
to try and unregister it first.
John
"00KobeBrian" wrote:

> I got it now. Thanks.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D0926A82-FDA8-467A-A56A-6DC1FBE3CE21@.microsoft.com...
>
>

import an excel file

I tried to import an excel file using import/export wizard. When I launch
it, it gives me an error:
The SSIS Runtime object could not be created. Verify that DTS.dll is avaible
and registered. The wizard cannot continue and will terminate.
Additional Information:
Unable to cast COM object of type ...
Can anyone please tell me how to fix this? Thanks.Hi
You should find dts.dll in C:\Program Files\Microsoft SQL
Server\90\DTS\Binn\DTS.dll. You could try unregistering in re-registering
this using regsvr32 e.g. regsvr32 C:\Program Files\Microsoft SQL
Server\90\DTS\Binn\DTS.dll
Run this with the /u flag will unload the dll if necessary.
If you are not using SP1 you may want to load this to see if the problem is
resolved. If this does not cure the issue please post error numbers and the
full error message and any information from the event log as well.
HTH
John
"00KobeBrian" wrote:
> I tried to import an excel file using import/export wizard. When I launch
> it, it gives me an error:
> The SSIS Runtime object could not be created. Verify that DTS.dll is avaible
> and registered. The wizard cannot continue and will terminate.
> Additional Information:
> Unable to cast COM object of type ...
>
> Can anyone please tell me how to fix this? Thanks.
>
>|||Just want to clarify.
So do you want me to run regsvr32 C:\Program Files\Microsoft SQL
Server\90\DTS\Binn\DTS.dll /u
Then run regsvr32 C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll
and see if it works?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D0926A82-FDA8-467A-A56A-6DC1FBE3CE21@.microsoft.com...
> Hi
> You should find dts.dll in C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll. You could try unregistering in re-registering
> this using regsvr32 e.g. regsvr32 C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll
> Run this with the /u flag will unload the dll if necessary.
> If you are not using SP1 you may want to load this to see if the problem
> is
> resolved. If this does not cure the issue please post error numbers and
> the
> full error message and any information from the event log as well.
> HTH
> John
> "00KobeBrian" wrote:
>> I tried to import an excel file using import/export wizard. When I launch
>> it, it gives me an error:
>> The SSIS Runtime object could not be created. Verify that DTS.dll is
>> avaible
>> and registered. The wizard cannot continue and will terminate.
>> Additional Information:
>> Unable to cast COM object of type ...
>>
>> Can anyone please tell me how to fix this? Thanks.
>>|||I got it now. Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D0926A82-FDA8-467A-A56A-6DC1FBE3CE21@.microsoft.com...
> Hi
> You should find dts.dll in C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll. You could try unregistering in re-registering
> this using regsvr32 e.g. regsvr32 C:\Program Files\Microsoft SQL
> Server\90\DTS\Binn\DTS.dll
> Run this with the /u flag will unload the dll if necessary.
> If you are not using SP1 you may want to load this to see if the problem
> is
> resolved. If this does not cure the issue please post error numbers and
> the
> full error message and any information from the event log as well.
> HTH
> John
> "00KobeBrian" wrote:
>> I tried to import an excel file using import/export wizard. When I launch
>> it, it gives me an error:
>> The SSIS Runtime object could not be created. Verify that DTS.dll is
>> avaible
>> and registered. The wizard cannot continue and will terminate.
>> Additional Information:
>> Unable to cast COM object of type ...
>>
>> Can anyone please tell me how to fix this? Thanks.
>>|||Hi
Does this mean it is working?
Running regsvr32 C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll
whilst it is registered would not cause any problems, but it is a good idea
to try and unregister it first.
John
"00KobeBrian" wrote:
> I got it now. Thanks.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D0926A82-FDA8-467A-A56A-6DC1FBE3CE21@.microsoft.com...
> > Hi
> >
> > You should find dts.dll in C:\Program Files\Microsoft SQL
> > Server\90\DTS\Binn\DTS.dll. You could try unregistering in re-registering
> > this using regsvr32 e.g. regsvr32 C:\Program Files\Microsoft SQL
> > Server\90\DTS\Binn\DTS.dll
> >
> > Run this with the /u flag will unload the dll if necessary.
> >
> > If you are not using SP1 you may want to load this to see if the problem
> > is
> > resolved. If this does not cure the issue please post error numbers and
> > the
> > full error message and any information from the event log as well.
> >
> > HTH
> >
> > John
> >
> > "00KobeBrian" wrote:
> >
> >> I tried to import an excel file using import/export wizard. When I launch
> >> it, it gives me an error:
> >>
> >> The SSIS Runtime object could not be created. Verify that DTS.dll is
> >> avaible
> >> and registered. The wizard cannot continue and will terminate.
> >>
> >> Additional Information:
> >> Unable to cast COM object of type ...
> >>
> >>
> >> Can anyone please tell me how to fix this? Thanks.
> >>
> >>
> >>
>
>

import a dynamic file name into a known table

How would I import a dynamic file name into a known table?

I have the file name in a variable. So what object in SSIS do I use to do this.

Thanks

If you just want to insert file name into table, why do you need SSIS? You can just use an insert command which takes the file name variable as parameter.|||

I have inherited a SSIS package that does massive amounts of importing, so I am trying to stay with the way it eas done.

Out of curiosity how woul I do what you said? How would I do it in SSIS?