Data script task - how to generate multiple rows from one row ? (string splitter)

Hi

in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").

We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.

How can we achieve that with a vb.net data script task ? (Or anything else ?)

best regards

ThibautJust take your source and throw it into a derived column transformation where you'll perform your string split to create new fields for each "token." Then you'll go into an unpivot transformation where you'll take the new columns and turn them into rows.

Why use a script task when you can use the optimized data flow tools as-is?|||

You need to create an asynchrnous transform. So add the transform, select and also setup columns as required. Ensure the SynchronousInputID of the output is set to 0, making it async.

Then read rows and add them to the output as required. Sum dummy code -

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

' Read the rows in...

While Row.NextRow() ' This happens once for each input row

' Do something here, and as required, add rows to the output, use a loop or whatever

For i As Int = i < 10

With OutputBuffer

.AddRow() ' Adding a new output row and setting values. Can do this as many times as we like, 0 or more times in the context of this input row loop interation

.Asset = Row.InputColumn

.Product = i

Next

End While

If Row.EndOfRowset Then

OutputBuffer.SetEndOfRowset()

End If

End Sub

|||Thanks a lot Darren ! That's really perfect (and thanks for the detailed sample, I really appreciate).

regards,

Thibaut|||Just be careful because some have tested the script task and it performs slower than the other, native dataflow tasks.|||Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?|||

DarrenSQLIS wrote:

Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?

Yep, no problem... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=857796&SiteID=1|||Hi Phil

thanks for your input as well. Like Darren, I agree that a good rule of thumb is that a pipeline component will work generally faster than a corresponding script component.

A script component is generally one-shot code, which generally doesn't get the same level of testing and optimization. In the article you point to, the script task benchmark shows an average CPU usage of 5% (which is often a sign of synchronization issues, contention etc). Here (like suggested by a commenter), a modification of the implementation (like handling sets of rows instead of one row at a time) would most likely boost the performance a lot.

So I wouldn't draw conclusions based on a single script example, given that each implementation is likely to vary a lot in terms of performance and memory consumption - just like any kind of code!

But anyway - it seems that I have two solutions for my problem now. I don't hesitate to use script task when they prove useful, but I first try to stick to the pipeline components.

I'll keep you posted!

thanks again for the input

Thibaut Barrère|||Hi

I've finished the job using a script task (I will report back the details later).

I'm curious about how it would have been possible to implement this using a derived column transformation (at first sight I couldn't find out).

Phil could you give a bit more details ? I could not find any real string splitting functions in SSIS - were you thinking of using a FIND function recursively (or any other feature I've missed ?)

cheers

Thibaut

data migration from sybase 8.0 to sqlserver 2005

Hi all,

Here i had a task to migrate sybase 8.0 database to mssqlserver 2005 .how can i migrate this using INTEGRATED SERVICES (SSIS) or any other options. .Please try to provide some basical info because i am new to sybase versions.

JSR2005 wrote:

Hi all,

Here i had a task to migrate sybase 8.0 database to mssqlserver 2005 .how can i migrate this using INTEGRATED SERVICES (SSIS) or any other options. .Please try to provide some basical info because i am new to sybase versions.

data load from the different server

I tried to load data from the table in different server.

If I just want to limit one year data ( using date_key in that table ) then what task do I need to do that?

Please let me know.

Thanks.

In the OLE DB Source or the DataReader source, add a WHERE clause to the SQL.|||

Thanks.

data import task

Hello - suggestions greatly appreciated.
I'm trying to import data from similar tables between a local
access database and a network accessible sql server.
After using the Microsoft SQL Server Management Studio import wizard
I get the following error message on execute.
- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)
Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
that the column metadata is valid.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
(61) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)Hi
I can't say exactly what the issue is, but searching google for your error
80040E21 seems to be related most of the time to the data or datatypes. You
may want to save the import as a SSIS package or create a SSIS package from
scratch and then you would have more control over it.
"segue" wrote:
> Hello - suggestions greatly appreciated.
> I'm trying to import data from similar tables between a local
> access database and a network accessible sql server.
> After using the Microsoft SQL Server Management Studio import wizard
> I get the following error message on execute.
> - Pre-execute (Error)
> Messages
> Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> 0x80040E21.
> An OLE DB record is available. Source: "Microsoft SQL Native Client"
> Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done.".
> (SQL Server Import and Export Wizard)
> Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> that the column metadata is valid.
> (SQL Server Import and Export Wizard)
> Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> (61) failed the pre-execute phase and returned error code 0xC0202025.
> (SQL Server Import and Export Wizard)
>|||Thanks for the response.
The problem after changing a few things has manifested into what seems to be
data type constraints.
Iâ've tried suggestions â' changing column values varchar(10) to varchar(MAX)
or whatever or running an sql delete or update at the old data to remove
nulls but Iâ'm still getting these null constraint errors even though a select
shows there are no null values in the old database.
If anything comes to mind regarding dealing with constraints - appreciated.
Thanks.
"John Bell" wrote:
> Hi
> I can't say exactly what the issue is, but searching google for your error
> 80040E21 seems to be related most of the time to the data or datatypes. You
> may want to save the import as a SSIS package or create a SSIS package from
> scratch and then you would have more control over it.
> "segue" wrote:
> > Hello - suggestions greatly appreciated.
> >
> > I'm trying to import data from similar tables between a local
> > access database and a network accessible sql server.
> >
> > After using the Microsoft SQL Server Management Studio import wizard
> > I get the following error message on execute.
> >
> > - Pre-execute (Error)
> > Messages
> > Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> > 0x80040E21.
> > An OLE DB record is available. Source: "Microsoft SQL Native Client"
> > Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> > errors. Check each OLE DB status value, if available. No work was done.".
> > (SQL Server Import and Export Wizard)
> >
> > Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> > that the column metadata is valid.
> > (SQL Server Import and Export Wizard)
> >
> > Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> > (61) failed the pre-execute phase and returned error code 0xC0202025.
> > (SQL Server Import and Export Wizard)
> >|||First verify that the SQL Agent Account has Full Permissions to the Access
database.
You can build a package as anybody, but it will be executed by the Agent.
Second, Build the package from the server it will be executed from.
At Pre-Execute the data has not become visible.
so the issue revolves around connection. Permissions or naming.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4BC6F16B-7941-4734-BD16-7FC4571D5417@.microsoft.com...
> Hi
> I can't say exactly what the issue is, but searching google for your error
> 80040E21 seems to be related most of the time to the data or datatypes.
> You
> may want to save the import as a SSIS package or create a SSIS package
> from
> scratch and then you would have more control over it.
> "segue" wrote:
>> Hello - suggestions greatly appreciated.
>> I'm trying to import data from similar tables between a local
>> access database and a network accessible sql server.
>> After using the Microsoft SQL Server Management Studio import wizard
>> I get the following error message on execute.
>> - Pre-execute (Error)
>> Messages
>> Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error
>> code:
>> 0x80040E21.
>> An OLE DB record is available. Source: "Microsoft SQL Native Client"
>> Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation
>> generated
>> errors. Check each OLE DB status value, if available. No work was done.".
>> (SQL Server Import and Export Wizard)
>> Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor.
>> Verify
>> that the column metadata is valid.
>> (SQL Server Import and Export Wizard)
>> Error 0xc004701a: Data Flow Task: component "Destination -
>> CustomerShipping"
>> (61) failed the pre-execute phase and returned error code 0xC0202025.
>> (SQL Server Import and Export Wizard)|||Hi
If this was a spreadsheet I would guess there are blank cells at the end of
the range, but as access is the data source I am not sure why this occurs. I
am not sure if you have already created the destination table, but you need
to make the column nullable if you want it to be added. Have you pre-created
the destination table or does the package create it?
Also check out the topic "Handling Errors in Data" in Books online which
will allow you to move any rows that are not inserted into the database
elsewhere so you can see what they are.
John
"segue" wrote:
> Thanks for the response.
> The problem after changing a few things has manifested into what seems to be
> data type constraints.
> Iâ've tried suggestions â' changing column values varchar(10) to varchar(MAX)
> or whatever or running an sql delete or update at the old data to remove
> nulls but Iâ'm still getting these null constraint errors even though a select
> shows there are no null values in the old database.
> If anything comes to mind regarding dealing with constraints - appreciated.
> Thanks.
>
> "John Bell" wrote:
> > Hi
> >
> > I can't say exactly what the issue is, but searching google for your error
> > 80040E21 seems to be related most of the time to the data or datatypes. You
> > may want to save the import as a SSIS package or create a SSIS package from
> > scratch and then you would have more control over it.
> >
> > "segue" wrote:
> >
> > > Hello - suggestions greatly appreciated.
> > >
> > > I'm trying to import data from similar tables between a local
> > > access database and a network accessible sql server.
> > >
> > > After using the Microsoft SQL Server Management Studio import wizard
> > > I get the following error message on execute.
> > >
> > > - Pre-execute (Error)
> > > Messages
> > > Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> > > 0x80040E21.
> > > An OLE DB record is available. Source: "Microsoft SQL Native Client"
> > > Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> > > errors. Check each OLE DB status value, if available. No work was done.".
> > > (SQL Server Import and Export Wizard)
> > >
> > > Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> > > that the column metadata is valid.
> > > (SQL Server Import and Export Wizard)
> > >
> > > Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> > > (61) failed the pre-execute phase and returned error code 0xC0202025.
> > > (SQL Server Import and Export Wizard)
> > >|||In the select convert the columns to isnull([Column],'ISNULL') for strings
Then after the import you should be able to better locate them.
But if the select is failing to return records becuase views with join
columns haveing nulls.
then you will have to fix the underlying views.
-Randy Pitkin
-Information Systems Consultant since 1983
"segue" <segue@.discussions.microsoft.com> wrote in message
news:34CC113F-B137-4EB9-A6C7-507D733B0088@.microsoft.com...
> Thanks for the response.
> The problem after changing a few things has manifested into what seems to
> be
> data type constraints.
> I've tried suggestions - changing column values varchar(10) to
> varchar(MAX)
> or whatever or running an sql delete or update at the old data to remove
> nulls but I'm still getting these null constraint errors even though a
> select
> shows there are no null values in the old database.
> If anything comes to mind regarding dealing with constraints -
> appreciated.
> Thanks.
>
> "John Bell" wrote:
>> Hi
>> I can't say exactly what the issue is, but searching google for your
>> error
>> 80040E21 seems to be related most of the time to the data or datatypes.
>> You
>> may want to save the import as a SSIS package or create a SSIS package
>> from
>> scratch and then you would have more control over it.
>> "segue" wrote:
>> > Hello - suggestions greatly appreciated.
>> >
>> > I'm trying to import data from similar tables between a local
>> > access database and a network accessible sql server.
>> >
>> > After using the Microsoft SQL Server Management Studio import wizard
>> > I get the following error message on execute.
>> >
>> > - Pre-execute (Error)
>> > Messages
>> > Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error
>> > code:
>> > 0x80040E21.
>> > An OLE DB record is available. Source: "Microsoft SQL Native Client"
>> > Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation
>> > generated
>> > errors. Check each OLE DB status value, if available. No work was
>> > done.".
>> > (SQL Server Import and Export Wizard)
>> >
>> > Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor.
>> > Verify
>> > that the column metadata is valid.
>> > (SQL Server Import and Export Wizard)
>> >
>> > Error 0xc004701a: Data Flow Task: component "Destination -
>> > CustomerShipping"
>> > (61) failed the pre-execute phase and returned error code 0xC0202025.
>> > (SQL Server Import and Export Wizard)
>> >|||Thanks for the great ideas. I'm building (through the wizard?) the
"package"?
I'm not sure what "package" means exactly (in what context).
As far as permissions go the access is local to my computer and the sql is
remote. I'm not using an account to the access db which doesn't seem
necessary and I am using an account naturally for the SQL server and
connection. I'm able to perform ado.net w/asp.net and sql queries with the
same account
Any tasks to disect what's below greatly appreciated.
At Pre-Execute the data has not become visible so the issue revolves around
connection, permissions or naming.
Regards
"Randy Pitkin" wrote:
> First verify that the SQL Agent Account has Full Permissions to the Access
> database.
> You can build a package as anybody, but it will be executed by the Agent.
> Second, Build the package from the server it will be executed from.
> At Pre-Execute the data has not become visible.
> so the issue revolves around connection. Permissions or naming.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4BC6F16B-7941-4734-BD16-7FC4571D5417@.microsoft.com...
> > Hi
> >
> > I can't say exactly what the issue is, but searching google for your error
> > 80040E21 seems to be related most of the time to the data or datatypes.
> > You
> > may want to save the import as a SSIS package or create a SSIS package
> > from
> > scratch and then you would have more control over it.
> >
> > "segue" wrote:
> >
> >> Hello - suggestions greatly appreciated.
> >>
> >> I'm trying to import data from similar tables between a local
> >> access database and a network accessible sql server.
> >>
> >> After using the Microsoft SQL Server Management Studio import wizard
> >> I get the following error message on execute.
> >>
> >> - Pre-execute (Error)
> >> Messages
> >> Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error
> >> code:
> >> 0x80040E21.
> >> An OLE DB record is available. Source: "Microsoft SQL Native Client"
> >> Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation
> >> generated
> >> errors. Check each OLE DB status value, if available. No work was done.".
> >> (SQL Server Import and Export Wizard)
> >>
> >> Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor.
> >> Verify
> >> that the column metadata is valid.
> >> (SQL Server Import and Export Wizard)
> >>
> >> Error 0xc004701a: Data Flow Task: component "Destination -
> >> CustomerShipping"
> >> (61) failed the pre-execute phase and returned error code 0xC0202025.
> >> (SQL Server Import and Export Wizard)
> >>
>
>|||The table is already created and the table's database is a 3rd party .NET
based app., so I'm trying to import into a table that already exists.
The columns for that table have keys that are not nullable of course. The
error messages haven't mentioned those columns. The columns that are
mentioned in the null value error messages I've tried adding values for to
that access table with no luck.
I can't change the columns I'm importing to other than value types and sizes
when I bring up their design view in VS2005.
Suggestions greatly appreciated - thanks.
"John Bell" wrote:
> Hi
> If this was a spreadsheet I would guess there are blank cells at the end of
> the range, but as access is the data source I am not sure why this occurs. I
> am not sure if you have already created the destination table, but you need
> to make the column nullable if you want it to be added. Have you pre-created
> the destination table or does the package create it?
> Also check out the topic "Handling Errors in Data" in Books online which
> will allow you to move any rows that are not inserted into the database
> elsewhere so you can see what they are.
> John
> "segue" wrote:
> >
> > Thanks for the response.
> >
> > The problem after changing a few things has manifested into what seems to be
> > data type constraints.
> >
> > Iâ've tried suggestions â' changing column values varchar(10) to varchar(MAX)
> > or whatever or running an sql delete or update at the old data to remove
> > nulls but Iâ'm still getting these null constraint errors even though a select
> > shows there are no null values in the old database.
> >
> > If anything comes to mind regarding dealing with constraints - appreciated.
> >
> > Thanks.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I can't say exactly what the issue is, but searching google for your error
> > > 80040E21 seems to be related most of the time to the data or datatypes. You
> > > may want to save the import as a SSIS package or create a SSIS package from
> > > scratch and then you would have more control over it.
> > >
> > > "segue" wrote:
> > >
> > > > Hello - suggestions greatly appreciated.
> > > >
> > > > I'm trying to import data from similar tables between a local
> > > > access database and a network accessible sql server.
> > > >
> > > > After using the Microsoft SQL Server Management Studio import wizard
> > > > I get the following error message on execute.
> > > >
> > > > - Pre-execute (Error)
> > > > Messages
> > > > Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> > > > 0x80040E21.
> > > > An OLE DB record is available. Source: "Microsoft SQL Native Client"
> > > > Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> > > > errors. Check each OLE DB status value, if available. No work was done.".
> > > > (SQL Server Import and Export Wizard)
> > > >
> > > > Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> > > > that the column metadata is valid.
> > > > (SQL Server Import and Export Wizard)
> > > >
> > > > Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> > > > (61) failed the pre-execute phase and returned error code 0xC0202025.
> > > > (SQL Server Import and Export Wizard)
> > > >|||Hi
Randy has given an option to overcome the potential nullability of the
columns, another would be to change the table you load into and allow null
columns etc and then you can view the data and sort it out or make the
process a two stage process permanently and rectify the data as you import it.
John
"segue" wrote:
> The table is already created and the table's database is a 3rd party .NET
> based app., so I'm trying to import into a table that already exists.
> The columns for that table have keys that are not nullable of course. The
> error messages haven't mentioned those columns. The columns that are
> mentioned in the null value error messages I've tried adding values for to
> that access table with no luck.
> I can't change the columns I'm importing to other than value types and sizes
> when I bring up their design view in VS2005.
> Suggestions greatly appreciated - thanks.
>
> "John Bell" wrote:
> > Hi
> >
> > If this was a spreadsheet I would guess there are blank cells at the end of
> > the range, but as access is the data source I am not sure why this occurs. I
> > am not sure if you have already created the destination table, but you need
> > to make the column nullable if you want it to be added. Have you pre-created
> > the destination table or does the package create it?
> >
> > Also check out the topic "Handling Errors in Data" in Books online which
> > will allow you to move any rows that are not inserted into the database
> > elsewhere so you can see what they are.
> >
> > John
> >
> > "segue" wrote:
> >
> > >
> > > Thanks for the response.
> > >
> > > The problem after changing a few things has manifested into what seems to be
> > > data type constraints.
> > >
> > > Iâ've tried suggestions â' changing column values varchar(10) to varchar(MAX)
> > > or whatever or running an sql delete or update at the old data to remove
> > > nulls but Iâ'm still getting these null constraint errors even though a select
> > > shows there are no null values in the old database.
> > >
> > > If anything comes to mind regarding dealing with constraints - appreciated.
> > >
> > > Thanks.
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I can't say exactly what the issue is, but searching google for your error
> > > > 80040E21 seems to be related most of the time to the data or datatypes. You
> > > > may want to save the import as a SSIS package or create a SSIS package from
> > > > scratch and then you would have more control over it.
> > > >
> > > > "segue" wrote:
> > > >
> > > > > Hello - suggestions greatly appreciated.
> > > > >
> > > > > I'm trying to import data from similar tables between a local
> > > > > access database and a network accessible sql server.
> > > > >
> > > > > After using the Microsoft SQL Server Management Studio import wizard
> > > > > I get the following error message on execute.
> > > > >
> > > > > - Pre-execute (Error)
> > > > > Messages
> > > > > Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> > > > > 0x80040E21.
> > > > > An OLE DB record is available. Source: "Microsoft SQL Native Client"
> > > > > Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> > > > > errors. Check each OLE DB status value, if available. No work was done.".
> > > > > (SQL Server Import and Export Wizard)
> > > > >
> > > > > Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> > > > > that the column metadata is valid.
> > > > > (SQL Server Import and Export Wizard)
> > > > >
> > > > > Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> > > > > (61) failed the pre-execute phase and returned error code 0xC0202025.
> > > > > (SQL Server Import and Export Wizard)
> > > > >

data import task

Hello - suggestions greatly appreciated.
I'm trying to import data from similar tables between a local
access database and a network accessible sql server.
After using the Microsoft SQL Server Management Studio import wizard
I get the following error message on execute.
- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)
Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
that the column metadata is valid.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
(61) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)
Hi
I can't say exactly what the issue is, but searching google for your error
80040E21 seems to be related most of the time to the data or datatypes. You
may want to save the import as a SSIS package or create a SSIS package from
scratch and then you would have more control over it.
"segue" wrote:

> Hello - suggestions greatly appreciated.
> I'm trying to import data from similar tables between a local
> access database and a network accessible sql server.
> After using the Microsoft SQL Server Management Studio import wizard
> I get the following error message on execute.
> - Pre-execute (Error)
> Messages
> Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> 0x80040E21.
> An OLE DB record is available. Source: "Microsoft SQL Native Client"
> Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done.".
> (SQL Server Import and Export Wizard)
> Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> that the column metadata is valid.
> (SQL Server Import and Export Wizard)
> Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> (61) failed the pre-execute phase and returned error code 0xC0202025.
> (SQL Server Import and Export Wizard)
>
|||Thanks for the response.
The problem after changing a few things has manifested into what seems to be
data type constraints.
I’ve tried suggestions – changing column values varchar(10) to varchar(MAX)
or whatever or running an sql delete or update at the old data to remove
nulls but I’m still getting these null constraint errors even though a select
shows there are no null values in the old database.
If anything comes to mind regarding dealing with constraints - appreciated.
Thanks.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I can't say exactly what the issue is, but searching google for your error
> 80040E21 seems to be related most of the time to the data or datatypes. You
> may want to save the import as a SSIS package or create a SSIS package from
> scratch and then you would have more control over it.
> "segue" wrote:
|||First verify that the SQL Agent Account has Full Permissions to the Access
database.
You can build a package as anybody, but it will be executed by the Agent.
Second, Build the package from the server it will be executed from.
At Pre-Execute the data has not become visible.
so the issue revolves around connection. Permissions or naming.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4BC6F16B-7941-4734-BD16-7FC4571D5417@.microsoft.com...[vbcol=seagreen]
> Hi
> I can't say exactly what the issue is, but searching google for your error
> 80040E21 seems to be related most of the time to the data or datatypes.
> You
> may want to save the import as a SSIS package or create a SSIS package
> from
> scratch and then you would have more control over it.
> "segue" wrote:
|||Hi
If this was a spreadsheet I would guess there are blank cells at the end of
the range, but as access is the data source I am not sure why this occurs. I
am not sure if you have already created the destination table, but you need
to make the column nullable if you want it to be added. Have you pre-created
the destination table or does the package create it?
Also check out the topic "Handling Errors in Data" in Books online which
will allow you to move any rows that are not inserted into the database
elsewhere so you can see what they are.
John
"segue" wrote:
[vbcol=seagreen]
> Thanks for the response.
> The problem after changing a few things has manifested into what seems to be
> data type constraints.
> I’ve tried suggestions – changing column values varchar(10) to varchar(MAX)
> or whatever or running an sql delete or update at the old data to remove
> nulls but I’m still getting these null constraint errors even though a select
> shows there are no null values in the old database.
> If anything comes to mind regarding dealing with constraints - appreciated.
> Thanks.
>
> "John Bell" wrote:
|||In the select convert the columns to isnull([Column],'ISNULL') for strings
Then after the import you should be able to better locate them.
But if the select is failing to return records becuase views with join
columns haveing nulls.
then you will have to fix the underlying views.
-Randy Pitkin
-Information Systems Consultant since 1983
"segue" <segue@.discussions.microsoft.com> wrote in message
news:34CC113F-B137-4EB9-A6C7-507D733B0088@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> The problem after changing a few things has manifested into what seems to
> be
> data type constraints.
> I've tried suggestions - changing column values varchar(10) to
> varchar(MAX)
> or whatever or running an sql delete or update at the old data to remove
> nulls but I'm still getting these null constraint errors even though a
> select
> shows there are no null values in the old database.
> If anything comes to mind regarding dealing with constraints -
> appreciated.
> Thanks.
>
> "John Bell" wrote:
|||Thanks for the great ideas. I'm building (through the wizard?) the
"package"?
I'm not sure what "package" means exactly (in what context).
As far as permissions go the access is local to my computer and the sql is
remote. I'm not using an account to the access db which doesn't seem
necessary and I am using an account naturally for the SQL server and
connection. I'm able to perform ado.net w/asp.net and sql queries with the
same account
Any tasks to disect what's below greatly appreciated.
At Pre-Execute the data has not become visible so the issue revolves around
connection, permissions or naming.
Regards
"Randy Pitkin" wrote:

> First verify that the SQL Agent Account has Full Permissions to the Access
> database.
> You can build a package as anybody, but it will be executed by the Agent.
> Second, Build the package from the server it will be executed from.
> At Pre-Execute the data has not become visible.
> so the issue revolves around connection. Permissions or naming.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4BC6F16B-7941-4734-BD16-7FC4571D5417@.microsoft.com...
>
>
|||The table is already created and the table's database is a 3rd party .NET
based app., so I'm trying to import into a table that already exists.
The columns for that table have keys that are not nullable of course. The
error messages haven't mentioned those columns. The columns that are
mentioned in the null value error messages I've tried adding values for to
that access table with no luck.
I can't change the columns I'm importing to other than value types and sizes
when I bring up their design view in VS2005.
Suggestions greatly appreciated - thanks.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If this was a spreadsheet I would guess there are blank cells at the end of
> the range, but as access is the data source I am not sure why this occurs. I
> am not sure if you have already created the destination table, but you need
> to make the column nullable if you want it to be added. Have you pre-created
> the destination table or does the package create it?
> Also check out the topic "Handling Errors in Data" in Books online which
> will allow you to move any rows that are not inserted into the database
> elsewhere so you can see what they are.
> John
> "segue" wrote:
|||Hi
Randy has given an option to overcome the potential nullability of the
columns, another would be to change the table you load into and allow null
columns etc and then you can view the data and sort it out or make the
process a two stage process permanently and rectify the data as you import it.
John
"segue" wrote:
[vbcol=seagreen]
> The table is already created and the table's database is a 3rd party .NET
> based app., so I'm trying to import into a table that already exists.
> The columns for that table have keys that are not nullable of course. The
> error messages haven't mentioned those columns. The columns that are
> mentioned in the null value error messages I've tried adding values for to
> that access table with no luck.
> I can't change the columns I'm importing to other than value types and sizes
> when I bring up their design view in VS2005.
> Suggestions greatly appreciated - thanks.
>
> "John Bell" wrote:

Data Generator for Sql Server 2005.

Hi !!

I am given the task to make an application in C# of filling the database ( made in sql server 2005) so that we can afterwards use those records for mining etc.. I dont have the slightest clue of how to go about making the data generator. Any ideas?

Thanks .

Checkout out the Visual Studio for Database professional, it has several data generators included.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Data Flow task within For Each Loop not executing

All:

I am sure I am missing something really silly but I am not able to figure out what. The For Each Loop uses an ADO Enumerator and passes variable values to a data flow. In executing the package the loop runs fine but nothing is happening to the data flow. When I move the data flow out of the loop it runs fine. What is going on?

Thanks!

desibull

desibull wrote:

All:

I am sure I am missing something really silly but I am not able to figure out what. The For Each Loop uses an ADO Enumerator and passes variable values to a data flow. In executing the package the loop runs fine but nothing is happening to the data flow. When I move the data flow out of the loop it runs fine. What is going on?

Thanks!

desibull

If the dataflow uses variables that are passed to it via the ForEach loop, how does the dataflow work when it is moved out of the ForEach loop? Do the variables have package scope?

Regards

|||

Yes, the variables have package scope.

It is really odd. The control does not seem to be flowing to the data flow. It jsut sits there doing nothing while the loop finishes executing.

|||

desibull wrote:

Yes, the variables have package scope.

It is really odd. The control does not seem to be flowing to the data flow. It jsut sits there doing nothing while the loop finishes executing.

Strange. Try dragging another executable (I suggest an empty sequence container) into the ForEach loop and see if it executes.

-Jamie

|||

I am such a dodo! Basically there were no records to select. I change a variable and there were rows to select and now everything runs fine.

What is still odd is that even when there were no records to select the data flow still showed the yellow and green colors when places outside the loop but remained white while within the loop, and that is why I panicked.

Thanks again!

|||

desibull wrote:

I am such a dodo! Basically there were no records to select. I change a variable and there were rows to select and now everything runs fine.

What is still odd is that even when there were no records to select the data flow still showed the yellow and green colors when places outside the loop but remained white while within the loop, and that is why I panicked.

Thanks again!

Strange. They should still change colour. Can I suggest you raise this at connect.microsoft.com with a repro?

-Jamie

|||Still being a newbie to this forum could you explain what repro means and what is expected to be posted to connect? Is it a visual display of the flow?|||

desibull wrote:

Still being a newbie to this forum could you explain what repro means and what is expected to be posted to connect? Is it a visual display of the flow?

Sorry. "repro" means reproduction. i.e. Something that demonstrates teh problem and can be executed by Microsoft.

Connect is a place for posting what you think is a bug. You should post anything that helps explain the problem. Words, pictures, repro, whatever.

-Jamie

|||

Jamie Thomson wrote:

desibull wrote:

I am such a dodo! Basically there were no records to select. I change a variable and there were rows to select and now everything runs fine.

What is still odd is that even when there were no records to select the data flow still showed the yellow and green colors when places outside the loop but remained white while within the loop, and that is why I panicked.

Thanks again!

Strange. They should still change colour. Can I suggest you raise this at connect.microsoft.com with a repro?

-Jamie

I have a different viewpoint on this. Since the data flow task is never executing (there are no records, so the For Each loop isn't execute anything inside it), it shouldn't show green (or yellow, or red).

|||

jwelch wrote:

I have a different viewpoint on this. Since the data flow task is never executing (there are no records, so the For Each loop isn't execute anything inside it), it shouldn't show green (or yellow, or red).

John,

The dataflow would still have to execute in order for it to KNOW that there were no records. The same is true of the source adapters. Hence, if a source adapter is executing then it must create at least one buffer - even if the buffer is empty. If a buffer is created then I would expect the other components to process that buffer.

That's how I figure it in my head anyway.

-Jamie

|||

I agree, if the data flow is actually being run. If I am interpreting the OP's comments correctly, though, the Data Flow isn't executing because there were no rows retrieved in the ADO resultset that is driving the For Each loop, . If there are rows in the resultset that is used by the For Each, then the data flow should be executing and it should show green. If, however, there are no rows in the For Each resultset, it shouldn't be executing the data flow at all, so I would expect it to remain white.

|||

jwelch wrote:

I agree, if the data flow is actually being run. If I am interpreting the OP's comments correctly, though, the Data Flow isn't executing because there were no rows retrieved in the ADO resultset that is driving the For Each loop, . If there are rows in the resultset that is used by the For Each, then the data flow should be executing and it should show green. If, however, there are no rows in the For Each resultset, it shouldn't be executing the data flow at all, so I would expect it to remain white.

Ah OK. I'll guess we'll have to see if he replies to clarify

|||

John is right on the money. I observed that the data flow within the ForEach loop does not execute when the ADO Recordset driving the loop is empty.

But I am still unable to understand why the data flow will not execute at least once because the loop executes once using the default values of the variables. Now, using the default values the data flow does not pull any records but it should at least execute, right?

Anyways, my observation confirms John's viewpoint.

Thank you!

|||

There is no concept of default values. Yes, there are values stored there at design-time but they won't get used if you using another method to set them at execution-time.

-Jamie

Data Flow task within For Each Loop not executing

All:

I am sure I am missing something really silly but I am not able to figure out what. The For Each Loop uses an ADO Enumerator and passes variable values to a data flow. In executing the package the loop runs fine but nothing is happening to the data flow. When I move the data flow out of the loop it runs fine. What is going on?

Thanks!

desibull

desibull wrote:

All:

I am sure I am missing something really silly but I am not able to figure out what. The For Each Loop uses an ADO Enumerator and passes variable values to a data flow. In executing the package the loop runs fine but nothing is happening to the data flow. When I move the data flow out of the loop it runs fine. What is going on?

Thanks!

desibull

If the dataflow uses variables that are passed to it via the ForEach loop, how does the dataflow work when it is moved out of the ForEach loop? Do the variables have package scope?

Regards

|||

Yes, the variables have package scope.

It is really odd. The control does not seem to be flowing to the data flow. It jsut sits there doing nothing while the loop finishes executing.

|||

desibull wrote:

Yes, the variables have package scope.

It is really odd. The control does not seem to be flowing to the data flow. It jsut sits there doing nothing while the loop finishes executing.

Strange. Try dragging another executable (I suggest an empty sequence container) into the ForEach loop and see if it executes.

-Jamie

|||

I am such a dodo! Basically there were no records to select. I change a variable and there were rows to select and now everything runs fine.

What is still odd is that even when there were no records to select the data flow still showed the yellow and green colors when places outside the loop but remained white while within the loop, and that is why I panicked.

Thanks again!

|||

desibull wrote:

I am such a dodo! Basically there were no records to select. I change a variable and there were rows to select and now everything runs fine.

What is still odd is that even when there were no records to select the data flow still showed the yellow and green colors when places outside the loop but remained white while within the loop, and that is why I panicked.

Thanks again!

Strange. They should still change colour. Can I suggest you raise this at connect.microsoft.com with a repro?

-Jamie

|||Still being a newbie to this forum could you explain what repro means and what is expected to be posted to connect? Is it a visual display of the flow?|||

desibull wrote:

Still being a newbie to this forum could you explain what repro means and what is expected to be posted to connect? Is it a visual display of the flow?

Sorry. "repro" means reproduction. i.e. Something that demonstrates teh problem and can be executed by Microsoft.

Connect is a place for posting what you think is a bug. You should post anything that helps explain the problem. Words, pictures, repro, whatever.

-Jamie

|||

Jamie Thomson wrote:

desibull wrote:

I am such a dodo! Basically there were no records to select. I change a variable and there were rows to select and now everything runs fine.

What is still odd is that even when there were no records to select the data flow still showed the yellow and green colors when places outside the loop but remained white while within the loop, and that is why I panicked.

Thanks again!

Strange. They should still change colour. Can I suggest you raise this at connect.microsoft.com with a repro?

-Jamie

I have a different viewpoint on this. Since the data flow task is never executing (there are no records, so the For Each loop isn't execute anything inside it), it shouldn't show green (or yellow, or red).

|||

jwelch wrote:

I have a different viewpoint on this. Since the data flow task is never executing (there are no records, so the For Each loop isn't execute anything inside it), it shouldn't show green (or yellow, or red).

John,

The dataflow would still have to execute in order for it to KNOW that there were no records. The same is true of the source adapters. Hence, if a source adapter is executing then it must create at least one buffer - even if the buffer is empty. If a buffer is created then I would expect the other components to process that buffer.

That's how I figure it in my head anyway.

-Jamie

|||

I agree, if the data flow is actually being run. If I am interpreting the OP's comments correctly, though, the Data Flow isn't executing because there were no rows retrieved in the ADO resultset that is driving the For Each loop, . If there are rows in the resultset that is used by the For Each, then the data flow should be executing and it should show green. If, however, there are no rows in the For Each resultset, it shouldn't be executing the data flow at all, so I would expect it to remain white.

|||

jwelch wrote:

I agree, if the data flow is actually being run. If I am interpreting the OP's comments correctly, though, the Data Flow isn't executing because there were no rows retrieved in the ADO resultset that is driving the For Each loop, . If there are rows in the resultset that is used by the For Each, then the data flow should be executing and it should show green. If, however, there are no rows in the For Each resultset, it shouldn't be executing the data flow at all, so I would expect it to remain white.

Ah OK. I'll guess we'll have to see if he replies to clarify

|||

John is right on the money. I observed that the data flow within the ForEach loop does not execute when the ADO Recordset driving the loop is empty.

But I am still unable to understand why the data flow will not execute at least once because the loop executes once using the default values of the variables. Now, using the default values the data flow does not pull any records but it should at least execute, right?

Anyways, my observation confirms John's viewpoint.

Thank you!

|||

There is no concept of default values. Yes, there are values stored there at design-time but they won't get used if you using another method to set them at execution-time.

-Jamie

Data flow task to delete records and then insert records in transaction

HI,

I have been trying to solve the locking problem from past couple of days. Please help mee!!

Scenario:
--
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.

The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.

I dont know how to come out of this locking. Please help.

Thanks ,
SunilTry setting RetainSameConnection to TRUE on the connection manager.

|||It was already set to TRUE. Please help meeee..

|||Based on some other threads about similar issues, this may not be SSIS, but related to DTC and the core relational engine. You might try checking some of the other forums to see if they have any suggestions.

|||

Have you tried using a SQL Task (set based) for the deletes followed by your Insert Data Flow Task?

Larry

|||

Hey Larry,

Yes. I did use it.but still the blocking happens. Please suggest

Thanks,

Sunil

Data Flow Task Stuck

I have a simple data flow task setup...

2 ADO.NET connection managers, each referencing a DSN pointed to a Unidata database.

2 DataReader sources, each using a single ADO.NET connection managers, running a simple SELECT statement from a table.

I have a Union All transform setup to merge the data and write to a OLE DB Destination (SQL05 database)

When I run the package, each source will validate, but only one will execute. The other source will do nothing. The data source will be colored yellow, and will just sit there. The package will just sit, almost like it is waiting for input.

This behavior is not consistent, however. It varies which data source will hang, pretty much 50-50. About 25% of the time, both sources will execute, and all rows will be written to the destination.

Any help is appreciated.

thanks

Are the source connections pointing to the same database? I'm not familiar with Unidata, but it sounds like there might be some type of blocking issue, either in the database or in the ODBC driver. If you use two separate data flows, one per DataReader Source, does it consistently run successfully?|||No, they are not pointing to the same database, and I have tried independent data flows with the same results.

|||

When you tried independent data flows, did you add a precedence constraint so that they were executing sequentially rather than in parallel?

If the problem shows up when you are only running a single DataReader Source, I'd see if there are any known issues with the ODBC driver.

|||When I try them sequentially, it works correctly every time.

This is an acceptable workaround, but I am still curious to determine the reason for this strange behavio

|||

Corey M. wrote:

When I try them sequentially, it works correctly every time.

This is an acceptable workaround, but I am still curious to determine the reason for this strange behavio

My guess is that it's an ODBC driver issue. Perhaps table locking or something like that. Who knows. You'll have to check for any known issues with the ODBC driver provider.

|||

You may wish to try the flowsync transform, placing one FlowSync transform between each source adapter and the union all transform (for a total of two FlowSync transforms).

The FlowSync transform is a speed governor, so a particular source flow doesn't get too far ahead of the other one. For something like union all, one source outpacing another should not be an issue, but you could certainly give it a shot.

To install the transform, you'll need to copy the assembly (FlowSync.dll) into two places and add it to the toolbar.

1. For runtime purposes, copy the FlowSync.dll into the global assembly cache, which is "%windir%\assembly" or typically the directory c:\windows\assembly. For design-time purposes, copy the same FlowSync.dll into the Integration Service PipelineComponents directory, which is typically located at "%ProgramFiles%\Microsoft SQL Server\90\DTS\PipelineComponents", If you're running 64 bit, the design-time installation directory is typically located at "%ProgramFiles(x86)%\Microsoft SQL Server\90\DTS\PipelineComponents".

2. To add flowsync transform to the toolbar, open up BIDS, right click on the toolbox, select "Choose Items...", go to the tab SSIS Data Flow Items, and Select the FlowSync transform, which will then show up appear in the toolbox as an available transform.

Data Flow Task SQL strings

Hi,

I just wanna ask:

I'm creating an SSIS package, a Data Flow Task. I have used OLEDB Source connected to a SQL Server Destination. Now in my OLEDB Source, I have this SQL statement

SELECT FirstName, LastName, Age FROM Employees WHERE (Age > 10) AND (Age < 95)

But what I want is to have the last name and first name concatenated and in proper case(capitalize first letter of the firstname and surname). I also want to TRIM or remove the blank spaces of the field in my SQL statement. How I be able to do this?

I tried using proper(), trim() and ucase() like in MSAccess but no success.

Please help. Thanks in advance.

The OLE DB Source has to use the same syntax as used by the underlying DB engine - in this case SQL Server.

Have a look in BOL for RTRIM(), LTRIM(), SUBSTRING(), UPPER() LEFT(), REPLACE() to get you going.

Alternatively you could carry out this work in the SSIS data-flow using a Derived Column component.

-Jamie

|||Thanks for your reply. I was able to do it using Derived Column. Thanks again and more power...

Data flow task running very slow

Hello,

I developed an SSIS package doing a nightly load into a data warehouse. We have an 8 hour loading window - currently the package takes 16 hours to complete.

I isolated the problem to a Data Flow task where +-35% of the time is spent. This task is pretty straight forward:

- OLE DB source, reading +- 800,000 rows from a SQL server database

- 13 Lookups in sequence, to get surrogate keys from dimension tables. Lookups are all on GUIDS.

- An aggregation

- OLEDB target, fact table in a SQL server database.

It seems unreasonable for the this task to take over 5 hours. It spends the majority of time on the lookups - not so much at target, source and aggregation.

Any comments and advice will be greatly appreciated.

Thanks.

(PS some machine details:

OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name ARK-SQL
System Manufacturer HP
System Model ProLiant DL380 G5
System Type X86-based PC
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
BIOS Version/Date HP P56, 9/18/2006
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone South Africa Standard Time
Total Physical Memory 3,327.30 MB
Available Physical Memory 938.20 MB
Total Virtual Memory 1.10 GB
Available Virtual Memory 2.78 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys)

How many rows are the lookups caching? Are you selecting the whole table (All columns) or are you using a SQL query to specify only the columns you need.

Are you sure it is not your dest that is slow receiving the rows?
If it is, SSIS will slow down the rows retrieved from the source making it appear as if it is something else slowing it down.

Check the lookups. Are as few columns as possible being selected.
Push your rows into nothing such as a Konesans Trash Destination. Does it appear faster?

Also, if your lookup is caching alot of rows and the key you are caching is a GUID, that's a chunk of work to do.|||

You need to find where the bottleneck is. You could start measuring how fast the dataflow 'reads from source'; then how fast it does the transformations (since you have a fair amount of transformations, I would measure a several points); and finally measure the how fast it writes into the destination.

few tips:

The lookups could slow down performance if you are using partial cache. So, limit the number of columns and rows (by providing a select statement with a where clause if possible) and use full cache mode. This approach could generate another problem if memory in server is limited.

See if you can move the aggregation upstream and or limit the number of columns to be used as 'group by'. In general aggregations will perform better if the number of columns with fewer columns in the 'group by'.

If you use an OLE DB destination, try to stick with 'fast load' and set the interval commit to an acceptable range

Use DB profiler tools to measure the performance of each SQL statement use in the data flow (OLE DB source, lookups, etc)

This white paper has some other information

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx|||

I would also highly recommend taking a read of this:

Donald Farmer's Technet webcast

(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx)

-Jamie

|||

Hi Crispin,

Thanks for your response.

I solved the problem last night. The key was in the caching.

I modify the SQL statement in most of my lookups to handle inferred fact entries. I.e. a sale against an unknown customer gets linked to the 'UNKNOWN CUTOMER' dimension table entry. It seems that SSIS sets the caching to partial when you modify the lookup SQL. I assume the lookup had to go back to disk quite often and therefore high cost for big dimension tables (...of which I have quite a few).

I am achieving a +-500% performance increase by keeping the Lookup transform as it is and by setting the cahcing to full. I treat the inferred fact entries at a later stage in stead of at the point of lookup.

|||Good to hear it has approved, however...

The lookup does not change to partial cache when you specify a statement. Not sure why you say it has done this. Was the checkbox checked?

On the partial cache thing, you are correct. The lookup will check it's cache, if the key is found, use it, else run the query against SQL. This can become dog slow. Only time it has been a good thing, for me at least, is when you know your facts are going to be a very small portion of a really large dimension. That way, the first few queries are slow but you don't have to cache 13 million rows only to use 1000.

Data flow task reports different row count than actual rowcount

I have a data flow task that moves all the rows from 18 tables on a production server to a reporting services server. One table, which does not contain the most rows (about 650K rows) reports all the rows have been transferred. However, if I go in to the SQL Mgmt Studio and do a Select count(*) on the table, there are only 110k rows.

Has anyone else experienced this problem?

Thanks,

Nick Anzano

How does the data flow report that 650k rows have been transferred? Are you looking in the logs?

SSIS could certainly report that it has sent 650k to the database - what happens then is up to the database! Is the target database SQL Server? If so, use SQL Profiler to see what is happening when the rows are being sent to the server. Perhaps they are not being committed for some reason.

Donald Farmer

Data Flow Task question

Hi there. I'm trying to learn SSIS, please, help me. I have 2 questions:

1)
There are 2 databases on 2 different servers. I need to get data from Table1(database1) and put it to Table2(database2). But I have to insert rows, which ID is not exists in Table2. How Can I do necessary filter?

2)
In the OLE DB DataSource Component I have used SQL Command(it's simplified):

declare @.TmpTable TABLE (WorkCode int not null);

INSERT INTO @.TmpTable (WorkCode)
select WorkCode
from Table1

SELECT WorkCode
FROM @.TmpTable

SSIS Package works without any exception. But there is no any inserted record in destination table. If I try similar query without temporary table - it works good. Why?

1 - Create OLE DB source to 1st (source) database.
- Add a lookup transformation to select key from 2nd database on 2nd server. In that lookup join the key coming from the 1st database to the key in the 2nd
- Hook the error output (red arrow) from the lookup to an OLE DB destination which points to the 2nd database. This will insert records not found in the 2nd database.

2 - Try setting the RetainSameConnection property of the connection manager to true and see what happens.|||The first task works! Thanks! But the second is not. Any other ideas?
|||

Aliaksander Hmyrak wrote:

The first task works! Thanks! But the second is not. Any other ideas?

Good deal.

As for number 2, why are you using temp tables? 2 things - you can just write the query that inserts into the temp table as the source for the data flow. OR you could use yet another, initial, data flow to populate a SQL server table with the results you need in the 2nd data flow (the one you've already got written). After the two data flows, you could write an Execute SQL task in the control flow to truncate that "temporary" table.|||

At the start of your SQL statement add

Code Snippet

SET NOCOUNT ON

|||Thanks a lot !! Both methods work!

Data Flow Task Problem

I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
===================================

Failed to create the task. (Microsoft Visual Studio)

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

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskDesignerCouldNotBeInitialized&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskView.Microsoft.SqlServer.Dts.Runtime.Design.IDtsTaskUI.Initialize(TaskHost taskWrapper, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI()
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded()
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Try this.

Go to Windows services, check account under which "SQL Server Integration Services" run.

Switch it to "Local System account"

BAZ

|||

I don't see how the service will effect a designer issue. The service is only used for server storage support or during execution. Local System is not very good practice either, you should understand the security risks involved.

Unfortunately I don't have a solution to the original problem either. Maybe try a Reset Toolbox, as this should clean out any rubbish, and it seems like some tasks are rubbish, or at least not installed correctly. This may prompt something.

|||Hi,

I had the error below when adding data flow tasks in the designer, and Baz' advice worked. Must be some strange permission issue, but i do not know enough about this stuff yet to comment further.

Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.

ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)|||I recently ran into this problem and BAZ's suggestion fixed the issue.
|||BAZMIC solution did'nt worked for me... don't know what to do...
|||I had the exact same problem and BAZMIC's solution worked for me as well.|||

Regarding how the IS service affects the designer...

When the designer starts, it needs to know what IS components (e.g. data flow components, tasks, enumerators, etc) are available on the machine. The discovery process can delay the designer start-up enough to cause a bad user experience. To improve the start-up time, the service caches the information which can then be accessed by the designer and saves it from having to go through the discovery process each time. If the designer starts up and the service is not started, the service will attempt to start and fill its cache. Knowing this might help you troubleshoot these kinds of issues further.

|||

I have just upgraded my SQL 2005 to SP2.

I had the error stated in this thread, and tried Baz's solution.

However now I am getting this error message whenever I try to open a DataTask in SSIS:

TITLE: Microsoft Visual Studio

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)

Can anyone please offer some advice?

|||

Anonymous581024 wrote:

BAZMIC solution did'nt worked for me... don't know what to do...

I tried everything and it never worked. I finally deinstalled SQL Server 2005 Integration Services and reinstalled it.
|||

Thanks Bazmic it works with me |||This is apparantly in some cases a problem with the SP2 that was deployed. After installing this on the SQL Server 2005 I have had a variety of problems including this one.

Data Flow Task Problem

I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
===================================

Failed to create the task. (Microsoft Visual Studio)

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

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskDesignerCouldNotBeInitialized&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskView.Microsoft.SqlServer.Dts.Runtime.Design.IDtsTaskUI.Initialize(TaskHost taskWrapper, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI()
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded()
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Try this.

Go to Windows services, check account under which "SQL Server Integration Services" run.

Switch it to "Local System account"

BAZ

|||

I don't see how the service will effect a designer issue. The service is only used for server storage support or during execution. Local System is not very good practice either, you should understand the security risks involved.

Unfortunately I don't have a solution to the original problem either. Maybe try a Reset Toolbox, as this should clean out any rubbish, and it seems like some tasks are rubbish, or at least not installed correctly. This may prompt something.

|||Hi,

I had the error below when adding data flow tasks in the designer, and Baz' advice worked. Must be some strange permission issue, but i do not know enough about this stuff yet to comment further.

Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.

ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)|||I recently ran into this problem and BAZ's suggestion fixed the issue.
|||BAZMIC solution did'nt worked for me... don't know what to do...
|||I had the exact same problem and BAZMIC's solution worked for me as well.|||

Regarding how the IS service affects the designer...

When the designer starts, it needs to know what IS components (e.g. data flow components, tasks, enumerators, etc) are available on the machine. The discovery process can delay the designer start-up enough to cause a bad user experience. To improve the start-up time, the service caches the information which can then be accessed by the designer and saves it from having to go through the discovery process each time. If the designer starts up and the service is not started, the service will attempt to start and fill its cache. Knowing this might help you troubleshoot these kinds of issues further.

|||

I have just upgraded my SQL 2005 to SP2.

I had the error stated in this thread, and tried Baz's solution.

However now I am getting this error message whenever I try to open a DataTask in SSIS:

TITLE: Microsoft Visual Studio

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)

Can anyone please offer some advice?

|||

Anonymous581024 wrote:

BAZMIC solution did'nt worked for me... don't know what to do...

I tried everything and it never worked. I finally deinstalled SQL Server 2005 Integration Services and reinstalled it.
|||Thanks Bazmic it works with me |||This is apparantly in some cases a problem with the SP2 that was deployed. After installing this on the SQL Server 2005 I have had a variety of problems including this one.

Data Flow Task Problem

I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
===================================

Failed to create the task. (Microsoft Visual Studio)

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

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskDesignerCouldNotBeInitialized&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskView.Microsoft.SqlServer.Dts.Runtime.Design.IDtsTaskUI.Initialize(TaskHost taskWrapper, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI()
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded()
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Try this.

Go to Windows services, check account under which "SQL Server Integration Services" run.

Switch it to "Local System account"

BAZ

|||

I don't see how the service will effect a designer issue. The service is only used for server storage support or during execution. Local System is not very good practice either, you should understand the security risks involved.

Unfortunately I don't have a solution to the original problem either. Maybe try a Reset Toolbox, as this should clean out any rubbish, and it seems like some tasks are rubbish, or at least not installed correctly. This may prompt something.

|||Hi,

I had the error below when adding data flow tasks in the designer, and Baz' advice worked. Must be some strange permission issue, but i do not know enough about this stuff yet to comment further.

Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.

ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)|||I recently ran into this problem and BAZ's suggestion fixed the issue.|||BAZMIC solution did'nt worked for me... don't know what to do...|||I had the exact same problem and BAZMIC's solution worked for me as well.|||

Regarding how the IS service affects the designer...

When the designer starts, it needs to know what IS components (e.g. data flow components, tasks, enumerators, etc) are available on the machine. The discovery process can delay the designer start-up enough to cause a bad user experience. To improve the start-up time, the service caches the information which can then be accessed by the designer and saves it from having to go through the discovery process each time. If the designer starts up and the service is not started, the service will attempt to start and fill its cache. Knowing this might help you troubleshoot these kinds of issues further.

|||

I have just upgraded my SQL 2005 to SP2.

I had the error stated in this thread, and tried Baz's solution.

However now I am getting this error message whenever I try to open a DataTask in SSIS:

TITLE: Microsoft Visual Studio

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)

Can anyone please offer some advice?

|||

Anonymous581024 wrote:

BAZMIC solution did'nt worked for me... don't know what to do...

I tried everything and it never worked. I finally deinstalled SQL Server 2005 Integration Services and reinstalled it.

Data Flow Task Problem

I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
===================================

Failed to create the task. (Microsoft Visual Studio)

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

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskDesignerCouldNotBeInitialized&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskView.Microsoft.SqlServer.Dts.Runtime.Design.IDtsTaskUI.Initialize(TaskHost taskWrapper, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI()
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded()
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Try this.

Go to Windows services, check account under which "SQL Server Integration Services" run.

Switch it to "Local System account"

BAZ

|||

I don't see how the service will effect a designer issue. The service is only used for server storage support or during execution. Local System is not very good practice either, you should understand the security risks involved.

Unfortunately I don't have a solution to the original problem either. Maybe try a Reset Toolbox, as this should clean out any rubbish, and it seems like some tasks are rubbish, or at least not installed correctly. This may prompt something.

|||Hi,

I had the error below when adding data flow tasks in the designer, and Baz' advice worked. Must be some strange permission issue, but i do not know enough about this stuff yet to comment further.

Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.

ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)|||I recently ran into this problem and BAZ's suggestion fixed the issue.
|||BAZMIC solution did'nt worked for me... don't know what to do...
|||I had the exact same problem and BAZMIC's solution worked for me as well.|||

Regarding how the IS service affects the designer...

When the designer starts, it needs to know what IS components (e.g. data flow components, tasks, enumerators, etc) are available on the machine. The discovery process can delay the designer start-up enough to cause a bad user experience. To improve the start-up time, the service caches the information which can then be accessed by the designer and saves it from having to go through the discovery process each time. If the designer starts up and the service is not started, the service will attempt to start and fill its cache. Knowing this might help you troubleshoot these kinds of issues further.

|||

I have just upgraded my SQL 2005 to SP2.

I had the error stated in this thread, and tried Baz's solution.

However now I am getting this error message whenever I try to open a DataTask in SSIS:

TITLE: Microsoft Visual Studio

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)

Can anyone please offer some advice?

|||

Anonymous581024 wrote:

BAZMIC solution did'nt worked for me... don't know what to do...

I tried everything and it never worked. I finally deinstalled SQL Server 2005 Integration Services and reinstalled it.

Data Flow Task Problem

I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
===================================

Failed to create the task. (Microsoft Visual Studio)

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

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskDesignerCouldNotBeInitialized&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskView.Microsoft.SqlServer.Dts.Runtime.Design.IDtsTaskUI.Initialize(TaskHost taskWrapper, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI()
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded()
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Try this.

Go to Windows services, check account under which "SQL Server Integration Services" run.

Switch it to "Local System account"

BAZ

|||

I don't see how the service will effect a designer issue. The service is only used for server storage support or during execution. Local System is not very good practice either, you should understand the security risks involved.

Unfortunately I don't have a solution to the original problem either. Maybe try a Reset Toolbox, as this should clean out any rubbish, and it seems like some tasks are rubbish, or at least not installed correctly. This may prompt something.

|||Hi,

I had the error below when adding data flow tasks in the designer, and Baz' advice worked. Must be some strange permission issue, but i do not know enough about this stuff yet to comment further.

Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.

ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)|||I recently ran into this problem and BAZ's suggestion fixed the issue.
|||BAZMIC solution did'nt worked for me... don't know what to do...
|||I had the exact same problem and BAZMIC's solution worked for me as well.|||

Regarding how the IS service affects the designer...

When the designer starts, it needs to know what IS components (e.g. data flow components, tasks, enumerators, etc) are available on the machine. The discovery process can delay the designer start-up enough to cause a bad user experience. To improve the start-up time, the service caches the information which can then be accessed by the designer and saves it from having to go through the discovery process each time. If the designer starts up and the service is not started, the service will attempt to start and fill its cache. Knowing this might help you troubleshoot these kinds of issues further.

|||

I have just upgraded my SQL 2005 to SP2.

I had the error stated in this thread, and tried Baz's solution.

However now I am getting this error message whenever I try to open a DataTask in SSIS:

TITLE: Microsoft Visual Studio

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)

Can anyone please offer some advice?

|||

Anonymous581024 wrote:

BAZMIC solution did'nt worked for me... don't know what to do...

I tried everything and it never worked. I finally deinstalled SQL Server 2005 Integration Services and reinstalled it.
|||Thanks Bazmic it works with me |||This is apparantly in some cases a problem with the SP2 that was deployed. After installing this on the SQL Server 2005 I have had a variety of problems including this one.