HI

HI

I am new to sql server 2005. Just want to know how you perform this task.

I have to load my fact table from multiple flat files I am trying since last week when I connect multiple flat files to lookup it gives me error.

Any suggestions

Thanks

Don't you think it would help if you supplied the error message?

Do you honestly expect anyone to be able to help you based on the information you've provided here?

-Jamie

|||As Jamie pointed out, a bit more detail would help.

You trying to load multiple flat files into your fact table. What are you doing with the lookup? Where is the error happening?
What is the error message.

Ever taken your car to a mechanic and said it makes a funny noise. Did he look at you oddly?|||

Hi

I am really sorry I didnt provided the complete information. What I am doing is that connecting multiple flat files to lookup so that I can load my fact table. When I connect the second flat file it gives me error saying that

"cannot create connector The destination component doesnt have any avaliable inputs for use in creating a path"

Thanks

|||

adnan wali wrote:

Hi

I am really sorry I didnt provided the complete information. What I am doing is that connecting multiple flat files to lookup so that I can load my fact table. When I connect the second flat file it gives me error saying that

"cannot create connector The destination component doesnt have any avaliable inputs for use in creating a path"

Thanks

OK.

The lookup component can only take one input. If you want to pass the contents of many files to it then you will need a Union All component. Pass the output from all of your files to the Union All and then pass the output of the Union All to the Lookup.

Good luck.

-Jamie

HELP--SSIS Dataflow Task

Need help regarding ssis dataflow task

I need to create a ssis package. I want to import the data from a flat file to a table.

Lets say, the table has 5 columns -- col1, col2, col3, col4 , col5.(Assume that all columns can be NULLABLE) The datafile contains the data related to only three columns say col1, col2, col3. So when I use dataflow task to import the data from the file to the table, I will only get three columns, col1, col2, col3. Columns col4, col5 will be NULL.
However, I want to populate columns col4, col5 with some values which are stored in the variable.

IS there any way to do this?

Any help would be appreciated.

Thanks

vjai wrote:

Need help regarding ssis dataflow task

I need to create a ssis package. I want to import the data from a flat file to a table.

Lets say, the table has 5 columns -- col1, col2, col3, col4 , col5.(Assume that all columns can be NULLABLE) The datafile contains the data related to only three columns say col1, col2, col3. So when I use dataflow task to import the data from the file to the table, I will only get three columns, col1, col2, col3. Columns col4, col5 will be NULL.
However, I want to populate columns col4, col5 with some values which are stored in the variable.

IS there any way to do this?

Any help would be appreciated.

Thanks

What variable?

-Jamie

|||

Nullable flat file columns is a bit of a strange one, how do you tell the difference between null and an empty string? Having no column is not a valid way to represent null, that is an invalid file format! I suspect what you are asking cannot be done, certainly not if the format of the file changes.

if the format is static, but sometimes values are empty then you could use a Derived Column transform to test for this and substitute a variable value in place.

|||

hi Vijai

You can do this!! and its very simple

Steps

I assume that u have a source with 3 cols.

Now add a drived column transformation task create two new columns and in expression give corresponding variable .

Now if ur output will have 5 columns, 3 from ur source and 2 new columns having values from the variables.Do the necessary mapping with destination.

Thanks and Regards

Rahul Kumar, Software Engineer, India

HELP? Multiple processes

I have a foreach loop which contains a call to an Execute process task. I want the Execute process task to run once for each element in my foreach, but I need to run as many Execute process tasks as my server can handle. What is the best way to do this? Any help would be greatly appreciated.

What do you mean by "I need to run as many Execute process tasks as my server can handle?" Can you be more precise in what this means, and perhaps provide a little more context on what exactly you're trying to accomplish?|||

Unfortunately, the ForEach container does not currently support parallel execution of the loops. And since the Execute Process task waits for a return value from the executable it calls, you are stuck in a sequential mode.

You can work around this by using multiple ForEach loops, and dividing your workload between them. However, this may take a bit more manual tuning. Or you migh be able to launch the process asynchronously from a script task.

|||

This is a vendor api that generates the data I need for my warehouse. I have a exe that takes one parameter and populates the appropriate stage0 tables. Sometimes this api runs really fast and returns little, other times it take 20 minutes. I'd like to run as many occurences of this api as possible so as not to slow my loads.

|||This may not be an appropriate task for SSIS then. Can you call the EXE from a C# app that has a thread pool and then when all instances of the EXE have finished running, yuo can launch whatever SSIS packes need to run next?|||

I've condsidered running it this way by passing an array of parms and then threading. I appreciate the response. I was just hoping there was a simplier and easier to maintain solution.

HELP: How do I pass variables from Control Flow to Data Flow

I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.

I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.

Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?

Also, how would I update the original source table where source.id = objRects.id?

Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.

Thanks,

Steve

smcneese wrote:

I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.

I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.

Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?

Also, how would I update the original source table where source.id = objRects.id?

Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.

Thanks,

Steve

You can use the Derived Column Component to put variable values into the pipeline.

-Jamie

|||Use a multicast and an OLE DB Command to perform the update on the original table.|||

I looked at this, but the Derived Column Component requires an input also. How do I configure that?

Thanks,

Steve

|||

smcneese wrote:

I looked at this, but the Derived Column Component requires an input also. How do I configure that?

Thanks,

Steve

You can't use Derived Column Component as a source adapter so in this case you'll have to use a script component.

I would question as to whether this is really what you want to do though. Executing a data-flow just to process one row of data with one column in it seems like the proverbial sledgehammer to crack a nut. What are you trying to achieve exactly?

-Jamie

|||

I question it myself. ;) I am trying to perform what I thought was a simple task, but I don't know DTS/SSIS well enough to know the correct approach. I spent all day trying to read articles to understand, but I am just not getting it.

I have a table is SQL Server 2005 that gets status records written to it for events that occur on wireless phones. When a record is written into this table, I need to write a record into a table of a legacy application running in Access. My original thought was to write a managed stored procedure in C# but someone suggested SSIS so I thought I would give it a try. I would think this to be a simple problem to solve and exactly what SSIS is for, but I cannot figure it out.

Can you or someone point me in the right direction? My current approach used an Execute SQL task to read all of the records that have not been processed. Then I used a Foreach container thinking I could take each row, write it to Access and then update that original source row to indicate it was processed. I would prefer not to do it row by row, but the source table is constantly being written to so that needs to be accounted for. Any help for your experts would be greatly appreciated.

Thanks,

Steve

|||

smcneese wrote:

I question it myself. ;) I am trying to perform what I thought was a simple task, but I don't know DTS/SSIS well enough to know the correct approach. I spent all day trying to read articles to understand, but I am just not getting it.

I have a table is SQL Server 2005 that gets status records written to it for events that occur on wireless phones. When a record is written into this table, I need to write a record into a table of a legacy application running in Access. My original thought was to write a managed stored procedure in C# but someone suggested SSIS so I thought I would give it a try. I would think this to be a simple problem to solve and exactly what SSIS is for, but I cannot figure it out.

Can you or someone point me in the right direction? My current approach used an Execute SQL task to read all of the records that have not been processed. Then I used a Foreach container thinking I could take each row, write it to Access and then update that original source row to indicate it was processed. I would prefer not to do it row by row, but the source table is constantly being written to so that needs to be accounted for. Any help for your experts would be greatly appreciated.

Thanks,

Steve

I don't really see a problem updating the table while it is being written to. Modern RDBMSs are supposed to be handle multiple concurrent connections to the same resource - that's kinda the point.

However, you say that when a record is written to the table in SQL Server 2005 you need to write it to the Access application. That is inherently a real-time operation and SSIS is a tool for batch operations. Given that 10000foot view of the problem I don't think SSIS is what you want. If you do NOT need to write to the access application at the same time as the record arrives then this becomes more of a batch operation and SSIS will be more suitable.

-Jamie

|||

I should have stated "near real time". Doing this batch every x minutes is fine. My point was about concurrent operation on the RDBMS. If I use and ADO .NET Input on the Data Flow with the appropriate SQL Statement to select my records I can then pass the results of that to the OLE ADO Ouput for Access. Now how can I use that same result set to got back and update my source table "processed" column so they don't get picked up again the next time the batch process runs? Can I split the output of the ADO.NET Input and use it as input to two other processes? Is that the way to do it?

Thanks for your help Jamie!

|||

smcneese wrote:

I should have stated "near real time". Doing this batch every x minutes is fine. My point was about concurrent operation on the RDBMS. If I use and ADO .NET Input on the Data Flow with the appropriate SQL Statement to select my records I can then pass the results of that to the OLE ADO Ouput for Access. Now how can I use that same result set to got back and update my source table "processed" column so they don't get picked up again the next time the batch process runs? Can I split the output of the ADO.NET Input and use it as input to two other processes? Is that the way to do it?

Thanks for your help Jamie!

Yes, that is the way to do it. And jwelch replied on this thread telling you exactly how.

-Jamie

|||

All,

Thank you for all of your help. Everything finally clicked on how this all works. Solution working fine, tested and in production.

Thanks,

Steve

|||

smcneese wrote:

All,

Thank you for all of your help. Everything finally clicked on how this all works. Solution working fine, tested and in production.

Thanks,

Steve

Good news. A happy customer for a change

HELP: Failed to create the task. (Microsoft Visual Studio)

I created my first SSIS project in BI Dev Studio but get the following error when trying to add a data flow task to the Control container:

==================================

Failed to create the task. (Microsoft Visual Studio)

===================================

Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Executables.Add(String moniker)
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Any advice greatly appreciated..

cheers,
jordan

What build are you on? June CTP by chance?|||I just installed the June CPT and I am gettting this same error. Any ideas?

Thanks in advance.|||

Here's the entire error message:

===================================

Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Executables.Add(String moniker)
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

|||Yes it is June CTP Developer Edition.|||I also couldn't use the SQL Server Import and Export Wizard. The error that I get:

The SSIS data flow task could not be created. Verify that DTSPipeline.dll is available and registered. The wizard cannot continue and it will terminate.

Value does not fall within the expected range. (Microsoft.SQLServer.DTSRuntimeWrap)|||Hmmm, very odd. It sounds as though you have a corrupted installation. Is this the first time you've installed SQL Server on that machine, IOW is it a clean install? You might try to regsvr32 DTSPipeline.dll. Then try again. It appears that it's just not registered.|||

I have tried uninstalling and reinstalling the CPT 4 times. The last time I did a regclean, deleted left over directories and anything else I could thing of the make the install clean.

One thing I should mention is that I had the VSS.Net 2005 Beta 2 on my machine before I installed the SQL Server 2005 CPT and I had to remove it in order to go forward with the SQL install. Not sure if that left something’s behind. I did use the SQL removal tools to get things off my machine the last couple of times, but not the first. The first time I used the Add/Remove Programs applet, and those uninstalls failed, but the option to remove through the applet disappeared afterward.

Very frustrating. I have installed this on another machine without the problems; it's just that that machine is not my main workstation (and not portable).

I will try the regsvr32 suggestion and regasm on some of the other libraries if that doesn't help.

Thanks!

|||Tried that, no luck.

Any other suggestions?

Thanks!|||I did a search on my local hard drives and couldn't even find DTSPipeline.dll. The CTP version that we have were downloaded from MSDN subscribers download web site.

cheers,
j.|||Has there been any resolve to this error at all? I am close to giving up on the CTP entirely.

Any help would be greatly appreciated.|||Where are you getting the install from? What SKU are you installing? The DtsPipeline.dll should be installed on all SKUs. Are you installing Integration Services? Is there a Microsoft SQL Server\90\DTS folder on your machine? What's in there?|||yeah there is indeed a C:\Program Files\Microsoft SQL Server\90\DTS\Binn directory with DTSPipeline.dll in there. But still re-registering the DLL did not help.|||What control container are you trying to add the dataflow task to?|||Control Flow. But as I mentioned in my earlier post I couldn't even get Import/Export wizard to work within Management Studio.