Changing table selections

Hello,

I created a package using the import/export wizard in SSIS, that loads data from one database to the other. I am trying to find out how I can add and remove the tables that were originally selected when the package was created. I opened the package in BIDS, and I could not find that particular option. I know you can do this in 2000/DTS...

Any help would be appreciated...

Thank you,

David

That should be part of the data flow. when you open the package in BIDS; click in the dataflow tab.

Notice that is the new table has a diffrent structure; you will need to 'refresh' the metadata in the other components in the dataflow.

|||You may have a Transfer Objects task in your package, instead of a data flow. If you click that and hit F4 to bring up the Properties window, one of the properties is Database Objects. You can alter the list of items from there.

Changing SSIS package - new user question

I saved a simple export/import package from the import/export wizard to a .dtsx file/package. Now I want to change the package to delete/truncate all of the tables before adding data. (since this has 80 tables it would have been a pain to edit each of them individually)

How can I easily go about doing this with the SSIS package. I can't find out where this would actually be executed.

Also, how can I actually view the code behind the SSIS package.

Thanks.

fcb wrote:

I saved a simple export/import package from the import/export wizard to a .dtsx file/package. Now I want to change the package to delete/truncate all of the tables before adding data. (since this has 80 tables it would have been a pain to edit each of them individually)

How can I easily go about doing this with the SSIS package. I can't find out where this would actually be executed.

Execute SQL Task

fcb wrote:

Also, how can I actually view the code behind the SSIS package.

Thanks.

Right-click on the package in BIDS-->View Code

-Jamie

|||I assume I have to place the Execute SQL Task in the For Each Loop Container. Do I just place a truncate table statement in the SQL Statement property? Does the "for each loop container" ensure it truncates every table or is there some other coding that has to be done?

I don't know whether its the view I'm in but I can't actually find the View Code selection when I right click on the package.
|||

fcb wrote:

I assume I have to place the Execute SQL Task in the For Each Loop Container. Do I just place a truncate table statement in the SQL Statement property? Does the "for each loop container" ensure it truncates every table or is there some other coding that has to be done?

Well, why not just write 80 delete statements in a single task. It'll take a while but you'l only have to do it once!

fcb wrote:

I don't know whether its the view I'm in but I can't actually find the View Code selection when I right click on the package.

Try clicking the "View Code" button in the solution explorer

-Jamie

Changing smtp and from values

During the install wizard prompts where presented for a
SMTP address and a FROM value. I want to change these
values now, How do I do this?Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config
Be sure to make a copy first :)
Bruce L-C
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:268801c47030$1efc9f70$a501280a@.phx.gbl...
> During the install wizard prompts where presented for a
> SMTP address and a FROM value. I want to change these
> values now, How do I do this?|||Edit the RSReportServer.config file found in %PROGRAMFILES%\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\ folder:
<RSEmailDPConfiguration>
<SMTPServer></SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing></SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From></From>
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:268801c47030$1efc9f70$a501280a@.phx.gbl...
> During the install wizard prompts where presented for a
> SMTP address and a FROM value. I want to change these
> values now, How do I do this?

Changing destination database for SSIS Package

Hi,

I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.

I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename

and here is the error I get:

Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.

I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...

Any help would be appreciated :)

Change the database name in the ConnectionString property instead.|||

Can you help me with the syntax for that? Do I have to create an environment variable for connectionstring instead of initialcatalog?

I've tried this:

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(lo
cal);Initial Catalog=TestCo_T_TWC;Provider=SQLNCLI;Integrated Security=SSPI;Auto
Translate=false;"

And i guess it doesn't like the quotes or something, because it causes a syntax error, but when i try it without quotes, i get:

option "Source=(local);Initial" is not valid.

|||You may want to read through this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1043984&SiteID=1|||

Hi,

Thanks for the help. I read through that thread and it is very close to answering my question. What I don't get though is how to punctuate the connection string. In the examples from the other post, they didn't have a long connection string with spaces in it, so it didn't have any punctuation (or the ones that did were the ones that were causing the OP trouble, so it's hard to say if his punctuation was correct). Can you help me with where to put my quotation marks please?

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Conne
ctions[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"

The above gives me the error:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

You can see it appears to have taken my quotes from around the connection string and put them around the entire /set argument... So how do I fix that?

|||I think, you should surround each part of the SET argument in double quotes. Embedded quotes can (should?) be escaped.

/SET "parameter-to-set";"value-with-\"escaped double quotes\""

So, try:

/SET "\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString]";"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"|||

Hi Phil,

Thanks for your help. That didn't work either, it leads to the same error as before:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[Connectio
nString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integ
rated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

Which is wierd because it is ignroing the double quotes I put between the package.connections etc etc and the data source= etc etc

|||Try this instead of /SET... This should work, because the Execute Package Utility generated it for me:

/CONNECTION DestinationConnectionOLEDB;"\"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\""|||

Thanks Phil!

It's too late for me to name my firstborn after you, but I still really appreciate your help today!

Cheers

Changing destination database for SSIS Package

Hi,

I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.

I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename

and here is the error I get:

Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.

I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...

Any help would be appreciated :)

Change the database name in the ConnectionString property instead.|||

Can you help me with the syntax for that? Do I have to create an environment variable for connectionstring instead of initialcatalog?

I've tried this:

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(lo
cal);Initial Catalog=TestCo_T_TWC;Provider=SQLNCLI;Integrated Security=SSPI;Auto
Translate=false;"

And i guess it doesn't like the quotes or something, because it causes a syntax error, but when i try it without quotes, i get:

option "Source=(local);Initial" is not valid.

|||You may want to read through this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1043984&SiteID=1|||

Hi,

Thanks for the help. I read through that thread and it is very close to answering my question. What I don't get though is how to punctuate the connection string. In the examples from the other post, they didn't have a long connection string with spaces in it, so it didn't have any punctuation (or the ones that did were the ones that were causing the OP trouble, so it's hard to say if his punctuation was correct). Can you help me with where to put my quotation marks please?

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Conne
ctions[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"

The above gives me the error:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

You can see it appears to have taken my quotes from around the connection string and put them around the entire /set argument... So how do I fix that?

|||I think, you should surround each part of the SET argument in double quotes. Embedded quotes can (should?) be escaped.

/SET "parameter-to-set";"value-with-\"escaped double quotes\""

So, try:

/SET "\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString]";"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"|||

Hi Phil,

Thanks for your help. That didn't work either, it leads to the same error as before:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[Connectio
nString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integ
rated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

Which is wierd because it is ignroing the double quotes I put between the package.connections etc etc and the data source= etc etc

|||Try this instead of /SET... This should work, because the Execute Package Utility generated it for me:

/CONNECTION DestinationConnectionOLEDB;"\"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\""|||

Thanks Phil!

It's too late for me to name my firstborn after you, but I still really appreciate your help today!

Cheers

Changing data source programatically does not transfer data

I have created a SSIS package that transfer data from a Foxpro database to an instance of SQL Server 2005 Express. I used the wizard to create the package but I load and execute the package within a custom application that I have written in C#.

The way the custom application is intended to work is that the user can have the database in any location on the computer and all he has to do is specify the location then the application programatically changes the location of the source on the package that it has loaded and then execute it. When I initially run the package the first time (using the original path), it works fine and transfers the data. However, every subsequent time I run the application and specify a different path, the database on the SQL Server side gets created as expected but the data is not transfered!

Where am I going wrong? Do I need to save the package after I modify the source then reload and run it again or do i need to change something else in the Data Flow to make this work?

I think this may be the wrong approach. You can parameterize the package at execution time with the connection string of the source and destination. This is better, and easier, than modifying the package structure.

-Jamie

|||

That is exactly what I am doing Jamie. I am changing the Connection string property of the source connection. Still doesn't work. Check below the code snippet:

// Load the package

string sLPFPackage = @."C:\projects\CCS_DTS\CCS_DTS\LPFMigration.dtsx";

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Package oLPFPackage = app.LoadPackage(sLPFPackage, null);

oLPFPackage.UpdateObjects = true;

// Modify the source and target data locations

sDataSource = "Data Source=" + sLPFDataFolder + ";Provider=VFPOLEDB.1;Collating Sequence=MACHINE;";

ConnectionManager oDataConn = oLPFPackage.Connections["SourceConnectionOLEDB"];

DtsProperty prDataSource = oDataConn.Properties["ConnectionString"];

prDataSource.SetValue(oDataConn, sDataSource);

// Execute the package

oLPFPackage.Execute();

Any ideas?

|||

Actually that is exactly what I am suggesting you do NOT do. The package can be parameterized from the command-line when executed via dtexec.exe without actually changing the package itself. I don't know much about executing package from an application but I have no doubt that you will be able to do the same.

Explore the /SET option of dtexec.exe for more information: http://msdn2.microsoft.com/en-us/library/ms162810.aspx

-Jamie

|||Thanks jamie. Let me check it out. I will let you know if it helped.