Howto dynamically change the BulkInsertTableName

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Howto dynamically change the BulkInsertTableName

Author

Topic

GWL
Starting Member

5 Posts

Posted - 2007-03-19 : 10:01:57

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

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

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-19 : 10:50:44

Set a variable when loading the package then i the package use the variable name for the table in the destination using an expression.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GWL
Starting Member

5 Posts

Posted - 2007-03-20 : 03:06:33

Hi,

Do you have an example for the Data Flow task (this is not the Bulk Insert task) and property BulkInsertTableName in the SQL Server Destination data flow component?

Thanks in advance,

Geert

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-20 : 08:16:39

In the destination you should have a drop down list - this gives the option of selecting a table or selecting a variable which contains the table name.
If it doesn't then try a different destination type.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GWL
Starting Member

5 Posts

Posted - 2007-03-20 : 08:56:40

In the SQL Destination Editor, there is the 'Use a table or view' item. I don't see anything related to variables.

In the OLE DB Destination Editor, there is the Data access mode item. When selecting 'Table name or view name variable'. But the variable name list is empty.

Other destinations are irrelevant for this purpose.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-20 : 09:55:15

Have you defined a variable? Is it in scope? Is it a string?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GWL
Starting Member

5 Posts

Posted - 2007-03-20 : 11:06:50

Name of the W2003 system: SDPM01
Name of the SQLServer 2005 SP2 instance: GWLINST1
Name of database 1: TEST (with table 1: Test_Table1 and table 2: Test_table2) [tables are the same]
Name of database 2: DEV (with table 1: Test_Table1 and table 2: Test_table2) [tables are the same]
Name of database 3: PROD (with table 1: Test_Table1 and table 2: Test_table2) [tables are the same]
Goal: Executing a DTSX package where the servername, instancename, database name, tablename can be dynamically changed.

In the meantime, yes I've defined the variable sTableName (value= Test_table1, string) and can see it in the OLE DB Destination Editor.
OLE DB Connection manager: SDPM01\GWLINST1.TEST
Data access mode: Table name or view name variable - fast load
Variable name: User::sTableName

Test 1:
script:
C:
cd Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7 \Test7
DTExec /F "Package.dtsx"
echo %ERRORLEVEL%
pause

output in CMD box:
D:\gwl>C:

C:\>cd Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7 \Test7

C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7\Test7>DTExec /F "Package.dtsx"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 3:06:19 PM
Progress: 2007-03-20 15:06:20.03
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2007-03-20 15:06:20.03
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2007-03-20 15:06:20.75
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2007-03-20 15:06:20.78
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2007-03-20 15:06:20.80
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2007-03-20 15:06:20.81
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2007-03-20 15:06:20.81
Source: Data Flow Task
Prepare for Execute: 0% complete
End Progress
Progress: 2007-03-20 15:06:20.81
Source: Data Flow Task
Prepare for Execute: 50% complete
End Progress
Progress: 2007-03-20 15:06:20.83
Source: Data Flow Task
Prepare for Execute: 100% complete
End Progress
Progress: 2007-03-20 15:06:20.86
Source: Data Flow Task
Pre-Execute: 0% complete
End Progress
Progress: 2007-03-20 15:06:20.86
Source: Data Flow Task
Pre-Execute: 50% complete
End Progress
Progress: 2007-03-20 15:06:20.87
Source: Data Flow Task
Pre-Execute: 100% complete
End Progress
Progress: 2007-03-20 15:06:21.16
Source: Data Flow Task
Post Execute: 0% complete
End Progress
Progress: 2007-03-20 15:06:21.17
Source: Data Flow Task
Post Execute: 50% complete
End Progress
Progress: 2007-03-20 15:06:21.17
Source: Data Flow Task
Post Execute: 100% complete
End Progress
Progress: 2007-03-20 15:06:21.19
Source: Data Flow Task
Cleanup: 0% complete
End Progress
Progress: 2007-03-20 15:06:21.19
Source: Data Flow Task
Cleanup: 50% complete
End Progress
Progress: 2007-03-20 15:06:21.20
Source: Data Flow Task
Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:06:19 PM
Finished: 3:06:21 PM
Elapsed: 2.219 seconds

C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7\Test7>echo 0
0

Test 2:
script:
C:
cd Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7 \Test7
DTExec /F "Package.dtsx" /SET "\Package.Variables[User::sTableName].Value";"Test_Table2"
echo %ERRORLEVEL%
pause

output in CMD box:
D:\gwl>C:

C:\>cd Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7 \Test7

C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7\Test7>DTExec /F "Package.dtsx" /S
ET "\Package.Variables[User::sTableName].Value";"Test_Table2"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 3:11:32 PM
Warning: 2007-03-20 15:11:33.51
Code: 0x80012017
Source: Package
Description: The package path referenced an object that cannot be found: "\Package.Variables[User::sTableName].Value"
. This occurs when an attempt is made to resolve a package path to an object that cannot be found.End Warning
DTExec: Could not set \Package.Variables[User::sTableName].Value value to Test_Table2.
Started: 3:11:32 PM
Finished: 3:11:33 PM
Elapsed: 0.656 seconds

C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7\Test7>echo 1
1

Test 3:
Change the value in the SSIS package to Test_Table2
script:
C:
cd Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7 \Test7
DTExec /F "Package.dtsx"
echo %ERRORLEVEL%
pause

output in CMD box:
D:\gwl>C:

C:\>cd Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7 \Test7

C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7\Test7>DTExec /F "Package.dtsx"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 3:16:47 PM
Progress: 2007-03-20 15:16:48.17
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2007-03-20 15:16:48.19
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2007-03-20 15:16:48.37
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2007-03-20 15:16:48.41
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2007-03-20 15:16:48.41
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2007-03-20 15:16:48.42
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2007-03-20 15:16:48.44
Source: Data Flow Task
Prepare for Execute: 0% complete
End Progress
Progress: 2007-03-20 15:16:48.44
Source: Data Flow Task
Prepare for Execute: 50% complete
End Progress
Progress: 2007-03-20 15:16:48.44
Source: Data Flow Task
Prepare for Execute: 100% complete
End Progress
Progress: 2007-03-20 15:16:48.47
Source: Data Flow Task
Pre-Execute: 0% complete
End Progress
Progress: 2007-03-20 15:16:48.47
Source: Data Flow Task
Pre-Execute: 50% complete
End Progress
Progress: 2007-03-20 15:16:48.48
Source: Data Flow Task
Pre-Execute: 100% complete
End Progress
Progress: 2007-03-20 15:16:48.53
Source: Data Flow Task
Post Execute: 0% complete
End Progress
Progress: 2007-03-20 15:16:48.55
Source: Data Flow Task
Post Execute: 50% complete
End Progress
Progress: 2007-03-20 15:16:48.55
Source: Data Flow Task
Post Execute: 100% complete
End Progress
Progress: 2007-03-20 15:16:48.56
Source: Data Flow Task
Cleanup: 0% complete
End Progress
Progress: 2007-03-20 15:16:48.58
Source: Data Flow Task
Cleanup: 50% complete
End Progress
Progress: 2007-03-20 15:16:48.58
Source: Data Flow Task
Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:16:47 PM
Finished: 3:16:48 PM
Elapsed: 1.141 seconds

C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Test7\Test7>echo 0
0

==> Test_table2 is correctly populated.

But via the DTEXEC it doesn't work.
DTExec /F "Package.dtsx" /SET "\Package.Variables[User::sTableName].Value";"Test_Table2"

Do you know what is wrong here?

Thanks in advance,

Geert

GWL
Starting Member

5 Posts

Posted - 2007-03-20 : 11:19:09

This is working.

DTExec /F "Package.dtsx" /SET "\Package\Data Flow Task.Variables[User::sTableName].Value";"Test_Table3"

Cheers,

Geert

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-20 : 12:15:18

That's because you created the variable when you were in the scope of the data flow task. The previous one would have worked if it had package scope.

I always create variables to be global then I can write them to an audit table from the control flow.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources