Howto dynamically change the BulkInsertTableName via dtexec

Question

Howto dynamically change the BulkInsertTableName via dtexec

Geert Willems

Old Hand

Points: 307
More actions
March 19, 2007 at 6:25 am

#176527

Hi,
I'm novice to SSIS and looking for some help on SSIS dtexec (SQL Server 2005).
Is it possible to change the BulkInsertTableName when running a package via dtexec /SET?
My test scenario contains:
- SQLServer 2005 SP2, servername: SDPM01, instancename: GWLINST1, databasename 1: TEST, databasename 2: DEV, tablename 1: Test_Table1 (both in TEST and DEV database), tablename 2: Test_Table2 (both in TEST and DEV database)
- 1 Data Flow task in BIDS (SSIS)
- 1 Data Flow Source: Flat File Source (Flat File Connection Manager name: FTP File Output + CSV file with a few lines of data that needs to be inserted in a SQL Server table)
- 1 Data Flow Destination: SQL Server Destination (OLE DB Connection Manager name: SDPM01\GWLINST1.TEST&nbsp
I can dynamically change the name of the database via:
DTExec /F "Package.dtsx" /SET "\Package.Connections[SDPM01\GWLINST1.TEST].InitialCatalog;DEV"
How can I also dynamically change the table name where the data from the CSV file will be inserted?
DTExec /F "Package.dtsx" /SET ...
Thanks in advance,
Geert

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 1

Using SET in the command line is often not the best way to configure your packages. Reasons for this IMHO are that you are increasing the potential for error by configuring each command line individually; you are increasing the amount of effort and management by not using a global configuration schema; Only those who have access to the SQL Agent can change them if you are running from there. However this might not be significant in your environment...

In 2005 you now have the ability to create package configurations by a number of means.

Environment variables - this is held on the server itself and in a normal development environment is the ideal place to store a pointer to where the rest of the configurations will be held.
XML file - this is probably the easiest one to set up. The downside to this is that they are in human readeable format, which may not be a good thing in a security sensitive environment. (you can of course mitigate this by restricting the permission of the folder they are stored in, restricting the permission of the file itself or encrypting the files).
SQL Server table - it means that you can restrict access to the configurations using the SQL Server's authentication.
Using the SET command in the command line.

Whichever way you intend to dynamically configure it, the method of altering the table name would be something like this...

Open the SSIS package in the designer.
Create a string variable named [sTableName] (Ensure that you are using the right scope to expose it to the Bulk Insert task).
Highlight the bulk insert task so that its properties are displayed.
Expand the expressions box.
Create a new expression.
In the drop down properties window, select DestinationTableName
Click the elipsis and the expression editor will appear.
Expand the variables folder.
Drag your [user:sTableName] variable into the window.
Click OK
Whatever is in the [sTableName] variable will be the object that the BulkInsert task tries to execute against at run-time.
You can populate this variable by various methods. Either pass it in with the command line; use one of the other config methods mentioned above; use a for each container or write a script component.

Hope this helps

Kindest Regards,

Frank Bazan

Geert Willems Old Hand Points: 307 More actions · Answer 2

Hi Frank,

Thanks for the quick reply. Sorry as I'm novice ...

Concerning 1: You mean Microsoft Visual Studio?

Concerning 2: See screenshot. Is this correct? What do I need to fill in as value for the sTableName? I don't use a Bulk Insert task instead I use a Data Flow Task (with Flat File source), but I suppose the logic should be the same.

Thanks in advance,

Geert

Geert Willems Old Hand Points: 307 More actions · Answer 3

Concerning 6. DestinationTableName doesn't exist for the Data Flow task. It does exist for the Bulk Insert task. But I've read to not use the Bulk Insert task anymore as it will become obsolete and is replaced by the Daya Flow task.

Rgds,

Geert

Steve Lund Ten Centuries Points: 1266 More actions · Answer 4

I used an OLE DB Destination instead of a SQL Server Destination. Then you 'edit' the OLE DB Destination and choose 'Table name or view name variable' as the Data access mode. You are then given a list of available variables to assign to this. I couldn't find a way to assign a variable name to the BulkInsertTableName property of the SQL Server Destination.

Geert Willems Old Hand Points: 307 More actions · Answer 5

Hi,

There is nothing in the list of available variables.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 6

My apologies... I didn't read your initial post correctly.

One thing that you have to remember is that the meta data in the package can't be changed at run-time, so, if you are trying to load to tables with different column names or data types, it wouldn't work anyways.

However if this is not the case and it is simply the tablename that changes then this is how it is done.

As before create a string variable and populate it with the name of the table you want to be your destination.
In the OLE DB Destination, select data access mode [Table Name or View Name variable - fast load]
In the variable drop down select the name of the variable you have just created.
If you populated the variable with a Table Name that exists on the SQL Server you are connected to, you should be able to move to the mapping tab and complete the configuration.

Hope this helps

PS: The screenshots are not working.

Kindest Regards,

Frank Bazan