creating access database with tables fail in SSIS package

I'm writing a package for SSIS and need to create a destination access database with a table on the fly. I've tried the code below - whcih works to create a database - but it doesn't create a table in that database to send data to. Instead of tbl.Parentcatalog = cat I've also used cat.Tables.Append(tbl) but this fails with a type problem. What is going wrong here?

private static void CreateDatabase(string currentDirectory)

{

if (!File.Exists(currentDirectory + DESTINATIONNAME))

{

// Create Database

ADOX.Catalog cat = new ADOX.CatalogClass();

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + currentDirectory + DESTINATIONNAME);

// Need to add columns using CREATE TABLE

ADOX.Table tbl = new ADOX.TableClass();

tbl.Name = "Currency";

tbl.Columns.Append("CurrencyCode", DataTypeEnum.adVarChar, 3);

tbl.Columns.Append("Name", DataTypeEnum.adVarWChar, 16);

tbl.Columns.Append("ModifiedDate", DataTypeEnum.adDate, 24);

tbl.ParentCatalog = cat;

}

}

replaced adVarWChar with DataTypeEnum.adWChar and it worked.

Creating a table with a dual primary key

This question may be a little complicated.

I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:

OrderID Order_LineID
1 1
2 1
2 2
2 3
3 1
3 2
4 1

Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?

Thanks!!!!!Does it matter that the destination needs to start at 1 and be sequential? If not, just use the Identity value. Probably too simple.

If you do it in a cursor, you could use a counter and move each record one at a time and use your counter to create the line number.

You could create the line number field in your source and pre-populate it with a process to loop through and assign the line numbers just prior to dumping it to the destination.

Doing it within one SQL statement to move it from one to the other might be impossible.|||Yes, you can create primary key that consists of two columns, or composite primary key.
It is not possible to create dual promary key in SQL server. You may create multiple uniqe index/constraints. But for one talbe, there is only one primary key.|||It is not quite clear what you exactly want to do.

Do you want to add a new column for an existing table as a primary key or is it a whole new table?|||I think we are on the right track (sort of).

I my example, the Order ID's are already assigned (they basically use an Identity Key).

What I need is some sort of query (or activeX script) that will loop through each record, and assign the Order_Line_ID (By the way, I don't care how many SQL statements this takes, as long as it works).

I am querying the Order_Line table, and sorting it by the Order#, so the table will look like this at first:

LineID-Identity | Order#
1 | 12345
2 | 12345
3 | 12345
4 | 12346
5 | 12347
6 | 12347
7 | 12348

The idea is, an order may have multiple lines (you've probably ordered more than one item at a time from some site online)

The counter needs to start over at 1 when it comes to a new order. For example, if the first three records in the Order_Line table are from Order# 12345, the first three rows would be numbered 1, 2 and 3, respectively (that should make sense). Now the tricky part: If the next Order # is 12346, the counter would start at 1 again, like this.

LineID-Identity | Order# | OrderLine#
1 | 12345 | 1
2 | 12345 | 2
3 | 12345 | 3
4 | 12346 | 1
5 | 12347 | 1
6 | 12347 | 2
7 | 12348 | 1

As you can see, this is a new column, so no data is being removed. How do you do this?|||Within SQL, use a cursor and loop through a sorted list of the line items. Use a "current" and "previous" variable to hold the order ID and a counter to hold the new line number. Create the field (null at first), loop through the cursor and using the logic of comparing the last order ID to the current one, assign the line number from the counter, increment the counter, etc. Look up the cursor options you'll need to use to make the cursor editable.

Creating a table in Access from an SSIS package

I need to run a make-table query against an Access database out of an SSIS package. I tried to do this with an OLE DB Command Task but it fails to create the table even though the task execution comes back successful. Any thoughts?

Did you tried with a script task ? Use the excel connection from the Connection Manager to connect to your Access DB and execute your create table query from a OleDbCommand object.

I've not tried that method, it just a thought.

sql

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 semaphore file on a network drive

Good Morning,

I'm hoping that someone can help me. I have a SQL 2005 SSIS package that will run Friday mornings to empty/load a table with data from another database. On Friday evenings I'll need to run another package, but want to make sure the table load completed prior to launch. For this I planned to use a file watcher task, however I cannot for the life of me figure out how to output a 'done' semaphore, from the morning job, to a networked drive.

A file system task will not work because there is not a 'create file' option. I do not have an existing file that I can rename either.

I tried an execute process task running cmd.exe with the following argument:

Code Snippet

echo Done> \\NetworkedServer\ftproot\Load.Done

This fails because UNC paths are not recognized. (The package executes from another server so I cannot use a local path, nor am I allowed to set-up a local share.)

Can someone offer an alternative suggestion? I'm really hoping this is easier than I'm making it.

Thank you in advance,

Roger

Why not have the first package write a value to a SQL table that the second package queries?|||You coud try a simialr aproach using a table. You can update or insert a row to indicate the status of the process. Then the next package will query that table and decide whether to run or not.

Creating a Script component using SSIS Object model

I had a SSIS Package which was developed on Beta Version Of SSIS, Now We have Standard Edition Of SSIS(Sql2k5) Installed.
In the Package we have One Flat file Source, One Script Component and A Sql Server as Target, We are programaticaly creating the package, Now When we open this Package in the Designer and Try to Run We get this error
"The script component is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Component Editor by clicking Design Script button to cause binary code to be generated. "
Now when we just open the Script Designer and close it, The package runs.
I found the Error description at this link
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.hresults.dts_e_binarycodenotfound.aspx
but could not find any help on this.

Same thing use to work on Beta version now it is now working.
From my understanding the problem is that in beta version, the script use to run at runtime, Now what they have done is that they generate a compiled code out of script and run.
My problem is that since i am using C# code to generate the package, How Will I Create a Binary Code out of Script.

Creating the binary code is very easy. Go into the script component, click on "Script..." button to bring up VSA (i.e. the script editor) and then close VSA down again. This will create the binary code for you.

-Jamie

|||

What changed is that the default value of the Precompile property changed from False to True.

If you are not precompiling your scripts, all you need to do is change the value of that property on the Script component. Note that this will give you a significantly smaller package file, but you'll pay a small performance penalty at runtime.

-Doug

Creating a Proxy Account

I am trying to run SSIS packages under SQL Server Agent 2005 and I keep getting a package failed error in the event viewer.

I've heard that I need to set up a proxy account. I have found the following code and need a little explanation on what all the parts mean since I am very new to this:

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

Let's say for the sake of argument my domain is called CompanyInc and I log into windows with my name Philip_Jaques and my password is badpassw0rd. Would I modify the above code this way to create my proxy?

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'CompanyInc\Philip_Jaques', secret = 'badpassw0rd'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name='Philip_Jaques', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

Also, when I create this proxy account where in SQL Server 2005 can I go to view it and its properties? And assuming I get the proxy account set up correctly, how do I get my current jobs to start using it so they will successfully run?

Thanks in advance for your help and advice!

I've never heard of having to create a proxy to get SqlAgent to run SSIS accounts. Sql Agent should be setup to run under a service account already, and will run SSIS packages no problem. Where did you find this information?|||

This article pretty much explains my problem:

http://support.microsoft.com/default.aspx/kb/918760

Creating a package with data flow task programmatically

I have created a simple package with 1 data flow task:
Source: data reader (to sql mobile)
Destination: oledb (to sql server)
And the aim is to do a "Select * from table_name" from source and move everything to destination database.

I have followed the steps in documentation, but the package fails! An exception is thrown with message: "Exception from HRESULT: 0xC0202072"

Since its the basic functionality of SSIS that I am implementing, it should work! I have spent 2 whole days trying to make this work and now its high time to ask for help. Can you please have a look!

Thanks,
Pragya

P.S. Any pointers to samples implementing a data flow task programmatically will help too!

Code:

Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;

//Add a SQL Mobile connection manager that is used by the component to the package.
ConnectionManager cm = package.Connections.Add("SQLMOBILE");
cm.Name = "SQL Mobile ConnectionManager";
cm.ConnectionString = "Data Source =D:\\Program Files\\Microsoft Visual Studio 8\\SmartDevices\\SDK\\SQL Server\\Mobile\\v3.0\\Northwind.sdf";

//Add a SQL Server connection manager that will be used later.
ConnectionManager cm1 = package.Connections.Add("OLEDB");
cm1.Name = "SQL Server Connection Manager";
cm1.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=tempdb;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

//Adding source component for SQL Mobile.
IDTSComponentMetaData90 component = dataFlow.ComponentMetaDataCollection.New();
component.Name = "ADONETSource";
component.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=9.0.242.0, Culture=neutral, blicKeyToken=89845dcd8080cc91";
CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
instance.SetComponentProperty("SqlCommand", "Select * from Employees");

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Adding destination component for SQL Server
IDTSComponentMetaData90 component1 = dataFlow.ComponentMetaDataCollection.New();
component1.Name = "SQL Server Destination";
component1.ComponentClassID = "DTSAdapter.SqlServerDestination.1";
CManagedComponentWrapper instance1 = component1.Instantiate();
instance1.ProvideComponentProperties();
if (component1.RuntimeConnectionCollection.Count > 0)
{
component1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
}
instance1.SetComponentProperty("BulkInsertKeepIdentity", true);
instance1.SetComponentProperty("BulkInsertKeepNulls", true);

//set path between components
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], component1.InputCollection[0]); //Assuming this is correct

// WORKS FINE TILL HERE -

// Reinitialize the metadata.
instance1.AcquireConnections(null);
instance1.ReinitializeMetaData(); //Throws exception. Message: "Exception from HRESULT: 0xC0202072" . Even if I reinitialize metadata after iterating through inputs of the component, the same exception is thrown at this statement.
instance1.ReleaseConnections();

// Iterate through the inputs of the component.
foreach (IDTSInput90 input in component1.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}

Well, 2 things I notice just from a first look is:

1. You don't set the ConnectionManagerID just the ConnectionManager, but both need to be set.

2. You don't specify a table or sqlstatement for your destination so how does the destination know what table to reinitialize its metadata for?

HTH,
Matt|||1. ConnectionManager.Id is a read only property. How to set it?
2. How can I do that?

Thanks|||

Ok! I got through one step. I added the following line:

instance1.SetComponentProperty("BulkInsertTableName","[Employees]");

to solve this.

However I have related questions,

1. Now the metadata can be initialized, but no rows are being transferred. Something is still amiss...

2. I need to manually create the employees table currently. How can i code the 'create table/view' option so that the table can be generated via code? Do I need to first compile the create table statement with column type information and then execute it using sql client? Is there a better way?

Thanks!

|||

Any information on this would help.

Thanks

Creating a Package of dtsx packages

Hi

I am trying to build a package that is comprised of 100+ dtsx packages but cannot seem to get it to work. I have created a new connection where the connectionmanagertype = file and the file path is equal to the folder in which my dtsx files are located. I (location = fileSystem). No matter what I do I get an access denied error that shows the folder location but no package. I manually typed the name of the package in the PackageName property and have pasted in the PackageID in the appropriate property as well but I don't see anything in the PackageNameReadOnly. I have read the MSDN information but I don't see a step by step way to build a package of packages against which I can compare. Can anyone set me straight?

Thanks.

Robin

You can't point a connection to a folder; it has to point to a package (.dtsx file).|||

Hi Cheese,

You can use Execute Package Tasks to do the trick. Here's how I build these:

1. Create a new SSIS package.

2. On the Control Flow, drag an Execute Package Task from the toolbox.

3. Double-click it to open the editor.

4. On the General page, give it a descriptive name.

5. On the Package page, click the COnnection dropdown and select <New Connection...>

6. Select Existing File for Usage Type and navigate to the file.

7. If the package is password protected, click the ellipsis (sp?) in the Password textbox and enter it (and confirm).

8. Click OK to exit the editor.

That should be enough to run a package.

Hope this helps,

Andy

|||Yep, I assumed Robin was trying to use the Execute Package tasks... If not, then Andy is spot on.|||

Hi

What I was actually trying to do was execute 115 packages (children) from a single package (parent) where I only used one connection manager. I thought that if I use the existing folder option I shouldn't have to create a connection manager for each one. I can do this writing code in an execute process task and the .net framework but that was more effort (troubleshooting properties) than it was worth. To me it would seem that I should be able to define a directory and recursively execute each *.dtsx package in a looping fashion in a simpler fashion. So far the only way I have gotten this to work is to 115 excecute package tasks with 115 connection managers (1 to 1 relationship of course) and then execute each one upon completion and out of process. This while successful seems to be a very poor way of doing things.

Thanks,

Robin

|||

Do you need to execute those packages serially or in parallel? If you need them to run in a serial fashion; you may use a ForEach loop container and 1 execute package task/Connection manager.

But if you need them to run in parallel or if you need to create precedence constraint between packages; I don't see a better way than having 115 execute package tasks and connection managers.

Notice that is you use Execute system task; the task will return succesfull right after sending the command; so the master package will not know whether the called package fail or not.

|||

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible. My solution right now is to have a connection manager for each source file (all 115 of them). This seems to be a really stupid thing to do.

Thanks,

Robin

|||

Cheese Bread wrote:

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible.

This sounds like the best approach for your scenario and is what I would have done. Why didn't it work?

-Jamie

|||

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

|||

Cheese Bread wrote:

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

Use a foreach loop to spin through your specific folder looking for *.dtsx files. Then, inside that foreach loop, you have one execute package task. Using the expressions feature of that task, you can set the Connection property to the variable populated by the foreach loop.

Creating a Package of dtsx packages

Hi

I am trying to build a package that is comprised of 100+ dtsx packages but cannot seem to get it to work. I have created a new connection where the connectionmanagertype = file and the file path is equal to the folder in which my dtsx files are located. I (location = fileSystem). No matter what I do I get an access denied error that shows the folder location but no package. I manually typed the name of the package in the PackageName property and have pasted in the PackageID in the appropriate property as well but I don't see anything in the PackageNameReadOnly. I have read the MSDN information but I don't see a step by step way to build a package of packages against which I can compare. Can anyone set me straight?

Thanks.

Robin

You can't point a connection to a folder; it has to point to a package (.dtsx file).|||

Hi Cheese,

You can use Execute Package Tasks to do the trick. Here's how I build these:

1. Create a new SSIS package.

2. On the Control Flow, drag an Execute Package Task from the toolbox.

3. Double-click it to open the editor.

4. On the General page, give it a descriptive name.

5. On the Package page, click the COnnection dropdown and select <New Connection...>

6. Select Existing File for Usage Type and navigate to the file.

7. If the package is password protected, click the ellipsis (sp?) in the Password textbox and enter it (and confirm).

8. Click OK to exit the editor.

That should be enough to run a package.

Hope this helps,

Andy

|||Yep, I assumed Robin was trying to use the Execute Package tasks... If not, then Andy is spot on.|||

Hi

What I was actually trying to do was execute 115 packages (children) from a single package (parent) where I only used one connection manager. I thought that if I use the existing folder option I shouldn't have to create a connection manager for each one. I can do this writing code in an execute process task and the .net framework but that was more effort (troubleshooting properties) than it was worth. To me it would seem that I should be able to define a directory and recursively execute each *.dtsx package in a looping fashion in a simpler fashion. So far the only way I have gotten this to work is to 115 excecute package tasks with 115 connection managers (1 to 1 relationship of course) and then execute each one upon completion and out of process. This while successful seems to be a very poor way of doing things.

Thanks,

Robin

|||

Do you need to execute those packages serially or in parallel? If you need them to run in a serial fashion; you may use a ForEach loop container and 1 execute package task/Connection manager.

But if you need them to run in parallel or if you need to create precedence constraint between packages; I don't see a better way than having 115 execute package tasks and connection managers.

Notice that is you use Execute system task; the task will return succesfull right after sending the command; so the master package will not know whether the called package fail or not.

|||

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible. My solution right now is to have a connection manager for each source file (all 115 of them). This seems to be a really stupid thing to do.

Thanks,

Robin

|||

Cheese Bread wrote:

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible.

This sounds like the best approach for your scenario and is what I would have done. Why didn't it work?

-Jamie

|||

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

|||

Cheese Bread wrote:

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

Use a foreach loop to spin through your specific folder looking for *.dtsx files. Then, inside that foreach loop, you have one execute package task. Using the expressions feature of that task, you can set the Connection property to the variable populated by the foreach loop.

Creating a Package of dtsx packages

Hi

I am trying to build a package that is comprised of 100+ dtsx packages but cannot seem to get it to work. I have created a new connection where the connectionmanagertype = file and the file path is equal to the folder in which my dtsx files are located. I (location = fileSystem). No matter what I do I get an access denied error that shows the folder location but no package. I manually typed the name of the package in the PackageName property and have pasted in the PackageID in the appropriate property as well but I don't see anything in the PackageNameReadOnly. I have read the MSDN information but I don't see a step by step way to build a package of packages against which I can compare. Can anyone set me straight?

Thanks.

Robin

You can't point a connection to a folder; it has to point to a package (.dtsx file).|||

Hi Cheese,

You can use Execute Package Tasks to do the trick. Here's how I build these:

1. Create a new SSIS package.

2. On the Control Flow, drag an Execute Package Task from the toolbox.

3. Double-click it to open the editor.

4. On the General page, give it a descriptive name.

5. On the Package page, click the COnnection dropdown and select <New Connection...>

6. Select Existing File for Usage Type and navigate to the file.

7. If the package is password protected, click the ellipsis (sp?) in the Password textbox and enter it (and confirm).

8. Click OK to exit the editor.

That should be enough to run a package.

Hope this helps,

Andy

|||Yep, I assumed Robin was trying to use the Execute Package tasks... If not, then Andy is spot on.|||

Hi

What I was actually trying to do was execute 115 packages (children) from a single package (parent) where I only used one connection manager. I thought that if I use the existing folder option I shouldn't have to create a connection manager for each one. I can do this writing code in an execute process task and the .net framework but that was more effort (troubleshooting properties) than it was worth. To me it would seem that I should be able to define a directory and recursively execute each *.dtsx package in a looping fashion in a simpler fashion. So far the only way I have gotten this to work is to 115 excecute package tasks with 115 connection managers (1 to 1 relationship of course) and then execute each one upon completion and out of process. This while successful seems to be a very poor way of doing things.

Thanks,

Robin

|||

Do you need to execute those packages serially or in parallel? If you need them to run in a serial fashion; you may use a ForEach loop container and 1 execute package task/Connection manager.

But if you need them to run in parallel or if you need to create precedence constraint between packages; I don't see a better way than having 115 execute package tasks and connection managers.

Notice that is you use Execute system task; the task will return succesfull right after sending the command; so the master package will not know whether the called package fail or not.

|||

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible. My solution right now is to have a connection manager for each source file (all 115 of them). This seems to be a really stupid thing to do.

Thanks,

Robin

|||

Cheese Bread wrote:

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible.

This sounds like the best approach for your scenario and is what I would have done. Why didn't it work?

-Jamie

|||

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

|||

Cheese Bread wrote:

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

Use a foreach loop to spin through your specific folder looking for *.dtsx files. Then, inside that foreach loop, you have one execute package task. Using the expressions feature of that task, you can set the Connection property to the variable populated by the foreach loop.

Creating a Package in SQL Server (T-SQL)

Hello,
Can we create a new Package in SQL Server (T-SQL). Oracle has the option of creating a new package using 'CREATE PACKAGE'.
Is SQL Server has anything similar to this.
Hi Aparna,
SQL Server doesn't have the concept of a package. You can
group stored procedures in SQL Server but it's nothing like
having a package, doesn't have the functionality of packages
(i.e. no package level variables to be used by other
procedures in the package) - it's really not the same thing
at all.
I don't know if have seen the following or not but there is
chapter in the SQL Server 2000 resource kit on migrating
Oracle databases to SQL Server. It has a lot of useful
information to help with such a migration. You can find it
online at:
Chapter 7 - Migrating Oracle Databases to SQL Server 2000
http://www.microsoft.com/resources/d...rt2/c0761.mspx
-Sue
On Wed, 31 Mar 2004 22:56:09 -0800, Aparna
<aparna.shirodkar@.lycos.com> wrote:

>Hello,
>Can we create a new Package in SQL Server (T-SQL). Oracle has the option of creating a new package using 'CREATE PACKAGE'.
>Is SQL Server has anything similar to this.
|||Hi Sue,
Thanx once again for the help. I will go thru the link u mentioned. Let's hope our problem gets resolved.
|||Hi Aparna
Good try, hope you are fine. Just flashing old memories. bye
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Creating a Package in SQL Server (T-SQL)

Hello,
Can we create a new Package in SQL Server (T-SQL). Oracle has the option of
creating a new package using 'CREATE PACKAGE'.
Is SQL Server has anything similar to this.Hi Aparna,
SQL Server doesn't have the concept of a package. You can
group stored procedures in SQL Server but it's nothing like
having a package, doesn't have the functionality of packages
(i.e. no package level variables to be used by other
procedures in the package) - it's really not the same thing
at all.
I don't know if have seen the following or not but there is
chapter in the SQL Server 2000 resource kit on migrating
Oracle databases to SQL Server. It has a lot of useful
information to help with such a migration. You can find it
online at:
Chapter 7 - Migrating Oracle Databases to SQL Server 2000
http://www.microsoft.com/resources/...r />
0761.mspx
-Sue
On Wed, 31 Mar 2004 22:56:09 -0800, Aparna
<aparna.shirodkar@.lycos.com> wrote:

>Hello,
>Can we create a new Package in SQL Server (T-SQL). Oracle has the option of
creating a new package using 'CREATE PACKAGE'.
>Is SQL Server has anything similar to this.|||Hi Sue,
Thanx once again for the help. I will go thru the link u mentioned. Let's ho
pe our problem gets resolved.|||Hi Aparna
Good try, hope you are fine. Just flashing old memories. bye
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

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 DTS package programaticaly

Hi,
I want to create a DTS package programatically (preferably in
C#.net),which will copy all my tables from a oracle database to my
sql-server database.
Can anybody help me doing this?
Thanks
PatnayakBOL documents all of the DTS API:
http://msdn.microsoft.com/library/e...spapps_21rn.asp

Assuming you are using SQL2000 there's an easy way to familiarise yourself
with the basics of DTS programming. Create a sample DTS package (either
using the Wizard or the Designer), open it up in the Designer and choose
Package/Save As... from the menu. Select "Visual Basic File" from the
Location dropdown and specify a file name. This will generate the Visual
Basic code to create and execute your package. You can then dissect, edit
and extend the code as required.

Inevitably there will be some work involved if you want to move the
generated code to C# but the example of how to manipulate the DTS objects
should give you a helpful start.

--
David Portas
----
Please reply only to the newsgroup
--|||To add to David's response, you can find a cookbook and examples for SQL
Server 2000 DTS with .NET at http://www.sqldev.net/dts/DotNETCookBook.htm.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pattnayak" <dpatnayak@.hotmail.com> wrote in message
news:a57a07c8.0401010301.3d175545@.posting.google.c om...
> Hi,
> I want to create a DTS package programatically (preferably in
> C#.net),which will copy all my tables from a oracle database to my
> sql-server database.
> Can anybody help me doing this?
> Thanks
> Patnayak

Creating a DTS package from a BAS file thru VB

Hello. ok, if I open a DTS package, and save it as a VB
BAS file, make some alterations to the BAS file, how do I
re-create the DTS package from that updated BAS file? Is
there any short sample code to do this that someone could
point me to? I'm not a VB coder, so just need a shell of
the VB code to run. THanks, BruceThe generated VB code will contain code to either execute or save the
package. The initial code will have the SaveToSqlServer line commented out.
You can remove the comment, specify the correct 'sa' password (and server or
other security credentials), comment out the Execute line and then run the
program. Code snippet example below. Note that you'll need to first delete
the package if you want to save it under the same name to the original
server.
You can find more information on the SaveToSQLServer method in the Books
Online.
'--
' Save or execute package
'--
goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
'goPackage.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
> Hello. ok, if I open a DTS package, and save it as a VB
> BAS file, make some alterations to the BAS file, how do I
> re-create the DTS package from that updated BAS file? Is
> there any short sample code to do this that someone could
> point me to? I'm not a VB coder, so just need a shell of
> the VB code to run. THanks, Bruce|||You da MAN!! actually the DAN, but.........
THanks, I'll try it out Monday... Bruce

>--Original Message--
>The generated VB code will contain code to either
execute or save the
>package. The initial code will have the SaveToSqlServer
line commented out.
>You can remove the comment, specify the correct 'sa'
password (and server or
>other security credentials), comment out the Execute
line and then run the
>program. Code snippet example below. Note that you'll
need to first delete
>the package if you want to save it under the same name
to the original
>server.
>You can find more information on the SaveToSQLServer
method in the Books
>Online.
>'--
>' Save or execute package
>'--
>goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
>'goPackage.Execute
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
VB[vbcol=seagreen]
do I[vbcol=seagreen]
Is[vbcol=seagreen]
could[vbcol=seagreen]
of[vbcol=seagreen]
>
>.
>

Creating a DTS package from a BAS file thru VB

Hello. ok, if I open a DTS package, and save it as a VB
BAS file, make some alterations to the BAS file, how do I
re-create the DTS package from that updated BAS file? Is
there any short sample code to do this that someone could
point me to? I'm not a VB coder, so just need a shell of
the VB code to run. THanks, Bruce
The generated VB code will contain code to either execute or save the
package. The initial code will have the SaveToSqlServer line commented out.
You can remove the comment, specify the correct 'sa' password (and server or
other security credentials), comment out the Execute line and then run the
program. Code snippet example below. Note that you'll need to first delete
the package if you want to save it under the same name to the original
server.
You can find more information on the SaveToSQLServer method in the Books
Online.
'--
' Save or execute package
'--
goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
'goPackage.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
> Hello. ok, if I open a DTS package, and save it as a VB
> BAS file, make some alterations to the BAS file, how do I
> re-create the DTS package from that updated BAS file? Is
> there any short sample code to do this that someone could
> point me to? I'm not a VB coder, so just need a shell of
> the VB code to run. THanks, Bruce
|||You da MAN!! actually the DAN, but.........
THanks, I'll try it out Monday... Bruce

>--Original Message--
>The generated VB code will contain code to either
execute or save the
>package. The initial code will have the SaveToSqlServer
line commented out.
>You can remove the comment, specify the correct 'sa'
password (and server or
>other security credentials), comment out the Execute
line and then run the
>program. Code snippet example below. Note that you'll
need to first delete
>the package if you want to save it under the same name
to the original
>server.
>You can find more information on the SaveToSQLServer
method in the Books[vbcol=seagreen]
>Online.
>'--
>' Save or execute package
>'--
>goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
>'goPackage.Execute
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
VB[vbcol=seagreen]
do I[vbcol=seagreen]
Is[vbcol=seagreen]
could[vbcol=seagreen]
of
>
>.
>

Creating a DTS package from a BAS file thru VB

Hello. ok, if I open a DTS package, and save it as a VB
BAS file, make some alterations to the BAS file, how do I
re-create the DTS package from that updated BAS file? Is
there any short sample code to do this that someone could
point me to? I'm not a VB coder, so just need a shell of
the VB code to run. THanks, BruceThe generated VB code will contain code to either execute or save the
package. The initial code will have the SaveToSqlServer line commented out.
You can remove the comment, specify the correct 'sa' password (and server or
other security credentials), comment out the Execute line and then run the
program. Code snippet example below. Note that you'll need to first delete
the package if you want to save it under the same name to the original
server.
You can find more information on the SaveToSQLServer method in the Books
Online.
'--
' Save or execute package
'--
goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
'goPackage.Execute
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
> Hello. ok, if I open a DTS package, and save it as a VB
> BAS file, make some alterations to the BAS file, how do I
> re-create the DTS package from that updated BAS file? Is
> there any short sample code to do this that someone could
> point me to? I'm not a VB coder, so just need a shell of
> the VB code to run. THanks, Bruce|||You da MAN!! actually the DAN, but.........
THanks, I'll try it out Monday... Bruce
>--Original Message--
>The generated VB code will contain code to either
execute or save the
>package. The initial code will have the SaveToSqlServer
line commented out.
>You can remove the comment, specify the correct 'sa'
password (and server or
>other security credentials), comment out the Execute
line and then run the
>program. Code snippet example below. Note that you'll
need to first delete
>the package if you want to save it under the same name
to the original
>server.
>You can find more information on the SaveToSQLServer
method in the Books
>Online.
>'--
>' Save or execute package
>'--
>goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
>'goPackage.Execute
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
>> Hello. ok, if I open a DTS package, and save it as a
VB
>> BAS file, make some alterations to the BAS file, how
do I
>> re-create the DTS package from that updated BAS file?
Is
>> there any short sample code to do this that someone
could
>> point me to? I'm not a VB coder, so just need a shell
of
>> the VB code to run. THanks, Bruce
>
>.
>

Creating a DTS package

I've created a DTS package and now I need to distribute it to
different servers.

I've been looking for a way to automatically/programatically create a
DTS package, but have not found anything definite.

From the DTS package itself, I see where I can save the dts package as
a structured file, with the name XXXXX.dts. Once I have that DTS
file, how dow I turn it back into a dts package in Enterprise manager?
I don't want to have to manually create the package for 300+
servers...

Thanks,
JenniferYou can load a DTS structured storage file from EM by right-clicking on
the Data Transformation Services folder and selecting Open Package. You
can then select Package --> Save As in order to save it locally.

This can be a bit tedious if you have a lot of servers. The VBScript
example below will load your DTS structured storage file and save it to
multiple servers using a trusted connection. See the Books Online for
details.

Dim DtsPackage
Set DtsPackage = CreateObject("DTS.Package2")
DtsPackage.LoadFromStorageFile "C:\MyDtsPackages\MyDtsPackage.dts", ""
DtsPackage.SaveToSqlServer "MyServer1", , , 256
DtsPackage.SaveToSqlServer "MyServer2", , , 256
DtsPackage.SaveToSqlServer "MyServer3", , , 256

Note that you can execute a DTS package directly from a structured
storage file using DTSRUN. If your servers have access to a shared
network location, you could execute the package with a UNC path rather
than saving the package locally on each server.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Jennifer" <jennifer1970@.hotmail.com> wrote in message
news:3358f49d.0311041237.15641d35@.posting.google.c om...
> I've created a DTS package and now I need to distribute it to
> different servers.
> I've been looking for a way to automatically/programatically create a
> DTS package, but have not found anything definite.
> From the DTS package itself, I see where I can save the dts package as
> a structured file, with the name XXXXX.dts. Once I have that DTS
> file, how dow I turn it back into a dts package in Enterprise manager?
> I don't want to have to manually create the package for 300+
> servers...
> Thanks,
> Jennifer

Creating a Connection to Database that Does Not Yet Exist

Hello All!
I am in the process of developing a DTS package. My
package will behave as follows:
1. Backup my live database.
2. Using the backup from Step 1., the backup will be
restored using a DIFFERENT name (NewDatabase).
3. Manipulate NewDatabase database.
How can I create a connection in my DTS Package when, at
the time I am creating the package, the NewDatabase
database does not exist? Do I have to use Disconnected
Edits? If so, how? Or, do I use Microsoft Data Link?
If so, how? Or, do I use something else. (As you can
tell from my questions, I am not an expert at this :-)
Any suggestions are greatly appreciated!!You should be able to connect to the master database to do what you need to
do initially.
Ray Higdon MCSE, MCDBA, CCNA
--
"ERR" <anonymous@.discussions.microsoft.com> wrote in message
news:ef7b01c3f1b0$3f8eff20$a301280a@.phx.gbl...
> Hello All!
> I am in the process of developing a DTS package. My
> package will behave as follows:
> 1. Backup my live database.
> 2. Using the backup from Step 1., the backup will be
> restored using a DIFFERENT name (NewDatabase).
> 3. Manipulate NewDatabase database.
> How can I create a connection in my DTS Package when, at
> the time I am creating the package, the NewDatabase
> database does not exist? Do I have to use Disconnected
> Edits? If so, how? Or, do I use Microsoft Data Link?
> If so, how? Or, do I use something else. (As you can
> tell from my questions, I am not an expert at this :-)
> Any suggestions are greatly appreciated!!|||I forgot to mention that I do have an initial connection,
the one to the original database. However, I need my DTS
package to create a connection to the NewDatabase
database once the task of restoring with a new name is
complete.
Thanks in advance!

>--Original Message--
>You should be able to connect to the master database to
do what you need to
>do initially.
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"ERR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ef7b01c3f1b0$3f8eff20$a301280a@.phx.gbl...
at
>
>.
>|||To do what? If it's only to run a sql script you can use workflow and
connect to the master database and then do the "use yournewdb" command after
you have created it
Ray Higdon MCSE, MCDBA, CCNA
--
"ERR" <anonymous@.discussions.microsoft.com> wrote in message
news:f43101c3f22e$9ff978d0$a301280a@.phx.gbl...
> I forgot to mention that I do have an initial connection,
> the one to the original database. However, I need my DTS
> package to create a connection to the NewDatabase
> database once the task of restoring with a new name is
> complete.
> Thanks in advance!
>
> do what you need to
> message
> at|||what you need to do as per Ray's suggestion is to create the dts package and
set it to connect to master, then create a dynamic task properties and a
global variable <dbname> to change the value of catalog property in
Disconnect Edit DE at run time.
then when you execute the dts package need to execute with the /A switch to
pass the <db> variable which u proide at run time see example below
dtsrun /S <servername> /E /N<packagename> /A <db>:8=newdatabase
this way u can connect to a database at runtime.
--
Olu Adedeji
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eR5zJ8y8DHA.3880@.tk2msftngp13.phx.gbl...
> To do what? If it's only to run a sql script you can use workflow and
> connect to the master database and then do the "use yournewdb" command
after
> you have created it
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "ERR" <anonymous@.discussions.microsoft.com> wrote in message
> news:f43101c3f22e$9ff978d0$a301280a@.phx.gbl...
>